Re: newbie: how to sort a database without extracting the data
MY first guess is that you simply need an ORDER BY field in yout (later, ad you put it) SELECT. However, the whole point of using a database such as MySQL is that you do not worry about how exactly your data is stored. there are many different tricks that a database can use to optimise both storage and retrieval, and it is the job of a good DBMS to implement as many of them as it can, and select the appropriate tricks to optimise your queries. However, in order to do this, you have to give it some hints, and the way you do this is by telling it to build indexes based on the fields which you intend to use for SLECTing data or for ORDERing, This allows the database to search un-ordered data in an ordered manner. The database automatically and invisibly maintains an index, or several indexes, on your data as you add and remove records. Once you have created the index, you need take no further action It sounds as if your field should be give a special kind of index called a PRIMARY KEY. This allows the database to ensure that entries ar unique, and to retrieve data very fast when selec ted or ordered by that column. You should search the MySQL manual (and poosibly the net) for PRIMARY KEY. I think you had the idea oc actually sorting the data in the file. This would be horrendously slow: basically, it would ahve to shuffle on average half the records in the database every time you did an insert or delete. You say that you don't want to sort the records during SELECT. But to do exactly this is what databases are designed to do: to accept data essentially randomy, build and maintain indexes on that data, and use those indexes at SELECT to produce a finely crafted subset of your data. Alec Christoph Lehmann [EMAIL PROTECTED] 04/05/2005 00:38 To mysql@lists.mysql.com, [EMAIL PROTECTED] cc Subject Re: newbie: how to sort a database without extracting the data thanks Damian but I don't understand this: My field according to which I want the database to be sorted IS an unique number. eg I have 1 ab 33 1 cd 21 1 ac 32 2 aa 22 2 cd 25 3 kw 03 3 ie 02 2 ei 05 2 wk 00 I need it in the form: 1 ab 33 1 cd 21 1 ac 32 2 aa 22 2 cd 25 2 ei 05 2 wk 00 3 kw 03 3 ie 02 what do you mean by adding an index thanks for your help cheers christoph Damian McMenamin wrote: add an index on the field. would be quickerthan any exporting importing. --- Christoph Lehmann [EMAIL PROTECTED] wrote: Hi I am really new to mysql. I need my database to be sorted according to one field. But since the database with 1200 records is huge, I don't want to do it using SELECT. What I need is just the stored database being sorted on hard-disk. Is there any way doing this like creating a new database and importing the old one but being sorted? many thanks for your kind help cheers christoph (p.s. I need this for later chunk-wise data-fetch with one chunk being homogenous in regard to one (the sorted) field) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Yours Sincerely, Damian McMenamin Analyst Programmer Melbourne Australia Cell: (61)040-0064107 Email: [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: Character Set Problem
Is it possible to change the character set just for an individual table and if so which character set should I try to display this european characters? Cheers, Lee - Original Message - From: Sumito_Oda [EMAIL PROTECTED] To: Lee Denny [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, May 03, 2005 1:42 PM Subject: Re: Character Set Problem Hello, Is the MySQL server that you are using MySQL4.1.x or MySQL5.0.x? As for most binarys of PHP and MySQL, the default charset of the MySQL connection client is set as 'latin1'. Therefore, if charset with the server is not 'latin1', it is necessary to set the MySQL connection client properly. It is whether to set to use the charset that you use by default, to compile the binary or to set the MySQL connection first by 'SET NAMES' syntax. http://dev.mysql.com/doc/mysql/en/set-option.html Regards, -- Sumito_Oda mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow queries, why?
Hello. We're running MySQL 4.11 on a machine with 2GB memory, the table is InnoDB with a compound primary key, and additional indexes on all rows with searchable options in the API. Any generic advice or admin tools would be great. Use EXPLAIN to determine how efficient your indexes are. Using a lot of keys could slow down the INSERT operations but fasten the SELECTs. InnoDB monitors might be helpful in your case as well. See: http://dev.mysql.com/doc/mysql/en/explain.html http://dev.mysql.com/doc/mysql/en/innodb-monitor.html Joseph Cochran [EMAIL PROTECTED] wrote: So here's my situation: we have a database that has a table of about 5 million rows. To put a new row into the table, I do an INSERT ... SELECT, pulling data from one row in the table to seed the data for the new row. When there are no active connections to the DB other than the one making the INSERT, it runs like a charm. But during normal daytime operation, when we run around 50 connections (most sleeping at any one time), it takes up to two minutes to do, and ends up locking any other inserts or updates against that table for the entire time. I'll get into more specifics if they're required, but I wanted to ask in general if MySQL has tools to diagnose this, or if anyone has had general situations like this. In SQL Server (which is where I have most of my experience) I could use the trace tool and the Query Analyzer to tell what the execution plan for the query was and thus what's stalling it (an index gone bad, a weird locking situation, etc). We're running MySQL 4.11 on a machine with 2GB memory, the table is InnoDB with a compound primary key, and additional indexes on all rows with searchable options in the API. Any generic advice or admin tools would be great. -- Joe -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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: Replication 4.1.11 to 5.0.4beta
Hello. I don't see correlations between error messages and replication of this table. In my opinion, there's something wrong with plugin_data.renderer table, check it. Usually you solve the replication problem starting with the binary logs examination (master and relay) with mysqlbinlog utility. Christian Meisinger [EMAIL PROTECTED] wrote: if i start to replicate the following table, i get this error: 050503 10:08:35 [ERROR] Key 1 - Found wrong stored record at 0 050503 10:08:35 [Note] Retrying repair of: './plugin_data/renderer' with keycache 050503 10:08:35 [ERROR] Key 1 - Found wrong stored record at 0 what's the problem??? -- CREATE TABLE `operatingsystem` ( `osid` int(10) unsigned NOT NULL default '0', `os` varchar(255) NOT NULL default '', PRIMARY KEY (`osid`), FULLTEXT KEY `os` (`os`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO `operatingsystem` VALUES (1, 'OS: Microsoft Windows XP Service Pack 2'); INSERT INTO `operatingsystem` VALUES (2, 'OS: Microsoft Windows 98 SE A'); INSERT INTO `operatingsystem` VALUES (3, 'OS: Microsoft Windows XP'); INSERT INTO `operatingsystem` VALUES (4, 'OS: Microsoft Windows XP Service Pack 1'); INSERT INTO `operatingsystem` VALUES (5, 'OS: Microsoft Windows 2000 Service Pack 4'); INSERT INTO `operatingsystem` VALUES (6, 'OS: Microsoft Windows Millennium Edition\n'); INSERT INTO `operatingsystem` VALUES (7, 'OS: Microsoft Windows 2000 Service Pack 2'); INSERT INTO `operatingsystem` VALUES (8, 'OS: Microsoft Windows XP Dodatek Service Pack 2'); INSERT INTO `operatingsystem` VALUES (9, 'OS: Microsoft Windows XP Dodatek Service Pack. 1'); INSERT INTO `operatingsystem` VALUES (10, 'OS: Microsoft Windows XP Service Pack 2, v.2082'); INSERT INTO `operatingsystem` VALUES (11, 'OS: Microsoft Windows Server; 2003 family'); INSERT INTO `operatingsystem` VALUES (12, 'OS: Microsoft Windows XP Service Pack 2, v.2096'); INSERT INTO `operatingsystem` VALUES (13, 'OS: Microsoft Windows 95'); INSERT INTO `operatingsystem` VALUES (14, 'OS: Microsoft Windows 2000'); INSERT INTO `operatingsystem` VALUES (15, 'OS: Microsoft Windows XP Szervizcsomag 1'); INSERT INTO `operatingsystem` VALUES (16, 'OS: Microsoft Windows 98'); INSERT INTO `operatingsystem` VALUES (17, 'OS: Microsoft Windows 95 OSR2 B'); INSERT INTO `operatingsystem` VALUES (18, 'OS: Microsoft Windows 2000 Service Pack 3'); INSERT INTO `operatingsystem` VALUES (19, 'OS: Microsoft Windows XP Service Pack 2, v.2055'); INSERT INTO `operatingsystem` VALUES (20, 'OS: Microsoft Windows XP Service Pack 2, v.2135'); INSERT INTO `operatingsystem` VALUES (21, 'OS: Microsoft Windows 95 OSR2 C'); INSERT INTO `operatingsystem` VALUES (22, 'OS: Microsoft Windows XP Szervizcsomag 2'); INSERT INTO `operatingsystem` VALUES (23, 'OS: Microsoft Windows 98 B'); INSERT INTO `operatingsystem` VALUES (24, 'OS: Microsoft Windows 98 A'); INSERT INTO `operatingsystem` VALUES (25, 'OS: Microsoft Windows 2000 Service Pack 1'); INSERT INTO `operatingsystem` VALUES (26, 'OS: Microsoft Windows XP Service Pack 2, v.2149'); INSERT INTO `operatingsystem` VALUES (27, 'OS: Microsoft Windows Millennium Edition\n A'); INSERT INTO `operatingsystem` VALUES (28, 'OS: Microsoft Windows 2000 Service Pack 3, RC 3.51'); INSERT INTO `operatingsystem` VALUES (29, 'OS: Microsoft Windows XP Service Pack 2, v.2162'); INSERT INTO `operatingsystem` VALUES (30, 'OS: Microsoft Windows 2000 Dodatek Service Pack. 2'); INSERT INTO `operatingsystem` VALUES (31, 'OS: Microsoft Windows XP Service Pack 1, v.1081'); INSERT INTO `operatingsystem` VALUES (32, 'OS: Microsoft Windows XP Service Pack 2, v.2126'); INSERT INTO `operatingsystem` VALUES (33, 'OS: Microsoft Windows XP Service Pack 1, v.1050'); INSERT INTO `operatingsystem` VALUES (34, 'OS: Microsoft Windows Millennium Edition'); INSERT INTO `operatingsystem` VALUES (35, 'OS: Microsoft Windows 95 b'); INSERT INTO `operatingsystem` VALUES (36, 'OS: Microsoft Windows 95 a'); INSERT INTO `operatingsystem` VALUES (37, 'OS: Microsoft Windows 2000 Dodatek Service Pack. 1'); INSERT INTO `operatingsystem` VALUES (38, 'OS: Microsoft Windows Server; 2003 family Service Pack 1, v.1433'); INSERT INTO `operatingsystem` VALUES (39, 'OS: Microsoft Windows Server; 2003 family Service Pack 1, v.1039'); INSERT INTO `operatingsystem` VALUES (40, 'OS: Microsoft Windows XP Service Pack 2, v.2138'); INSERT INTO `operatingsystem` VALUES (41, 'OS: Microsoft Windows 2000 Service Pack 4, RC 3.154'); INSERT INTO `operatingsystem` VALUES (42, 'OS: Microsoft Windows XP Service Pack 1, v.1105'); INSERT INTO `operatingsystem` VALUES (43, 'OS: Microsoft Windows Server; 2003 family Service Pack 1, v.1289'); INSERT INTO `operatingsystem` VALUES (44, 'OS:
Re: [Fwd: Re: Collation problems or messed joins?]
Hello. Please, send the output of the following statements: show variables like '%colla%'; show variables like '%char%'; Andr$s Villanueva [EMAIL PROTECTED] wrote: Anyone?? Original Message The tables are now entirely in utf8, and that is also the instance's default. I'm using the .net dll to connect and the connection string has charset = utf8. I'm testing everything with the query browser, and i get the exact same behaviour. Again, if anyone wants a script to recreate the tables with some data, I can send it to you instantly. Thanks Andr$s Villanueva Kevin Cowley wrote: Have you check that the collation for the text columns match the collation for the table and that you've set UTF8 for the query (set char set utf8). Kevin Cowley Product Development Alchemetrics Ltd SMARTER DATA , FASTER Tel: 0118 902 9000 (swithcboard) Tel: 0118 902 9099 (direct) Web: www.alchemetrics.co.uk Email: [EMAIL PROTECTED] -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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]
How can I find this data?
Hi, I have four tables among others in my database: Bookings, Work_Types, Practices Projects. Bookings occur in a Practice for a Project and have a Work_Type. A Practice can have many Bookings but must have one and only one Booking where the Work_Type.Day_Type = 1. This rule was introduced after the system was initially set up and I have a feeling there may be Practices that have no Day 1's. So how can I perform a query that returns all practices that have had bookings but no Day 1's for a particualar project? Please see table definitions below. Thanks very much for your help. mysql desc Bookings; +-+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-++ | Booking_ID | int(11) | | PRI | NULL| auto_increment | | Booking_Type| varchar(15) | | | Unavailability || | User_ID | int(11) | | | 0 || | Project_ID | int(11) | YES | | NULL|| | Rep_ID | int(11) | YES | | NULL|| | Practice_ID | int(11) | YES | | NULL|| | Booking_Creator_ID | int(11) | YES | | NULL|| | Booking_Creation_Date | datetime| YES | | NULL|| | Booking_Start_Date | datetime| | | -00-00 00:00:00 || | Booking_End_Date| datetime| | | -00-00 00:00:00 || | Booking_Completion_Date | date| YES | | NULL|| | Booking_Mileage | int(5) | YES | | NULL|| | Booking_Status | varchar(15) | | | Other || | Unavailability_ID | int(2) | YES | | NULL|| | Task_ID | int(11) | YES | | NULL|| | Work_Type_ID| int(2) | YES | | NULL|| | Additional_Notes| text| YES | | NULL|| | Pre_Event_Copy_Received_By_Scheduling | char(3) | YES | | NULL|| | Post_Event_Original_Completed_Form_Received | char(3) | YES | | NULL|| | Section_C | char(3) | YES | | NULL|| | Date_Difference | varchar(20) | | | n/a || | AU_Booking_ID | int(11) | YES | | NULL|| +-+-+--+-+-++ 22 rows in set (0.00 sec) mysql desc Projects; ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | Project_ID | int(11) | | PRI | NULL| auto_increment | | Project_Name | varchar(100) | | | | | | Client_ID | int(11) | | | 0 | | | Rep_Viewable | char(3) | | | Yes | | | Administrator_ID | int(11) | YES | | NULL| | | Administrator_Phone_Number | varchar(20) | | | | | | Project_Manager_ID_1 | int(11) | YES | | NULL| | | Project_Manager_ID_2 | int(11) | YES | | NULL| | ++--+--+-+-++ 8 rows in set (0.00 sec) mysql desc Practices; ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | Practice_ID|
Re: Tables lost in new location of database
Hello. Are you sure that you are running mysqld-max (mysqld-max-nt)? Are you able to select data from invisible tables? Is it possible that you have disabled symlinks? Does the problem remain with 4.1.11 version? I use mysql 4.0.23-nt on win XP. In order to shift some of my databases I followed the following steps as per a recent post: 1. I moved the folders containing the database files that I wanted to move to a new folder (from data to data2) after stopping mysql of course. 2. In the data folder I created a file named with the database (same as the folder names that I moved) prefixed with .sym. 3. Inside each of these .sym files I enterred (typed) in the exact path of the new folders and nothing else. 4. Restarted mysql Now when I give show databases; at the mysql prompt, all my database names are displayed including the ones I moved. Here is the issue: When I use one of the databases that I had moved and give: show tables I am getting an empty set. I know the data is there because the size of the .myd files is 50 MB - well none of the .myi or .frm file is missing or empty! Also I noticed that the .sym files have numbers auto-enterred before the file path I had specified - I guess it is in running mode. So how do I see my tables on the moved databases and query on them?? Thanks in advance, Anoop Anoop kumar V [EMAIL PROTECTED] wrote: -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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 unix socket and built-in defaults
Which cnf file (small,medium,large,huge) has params that are closer to mysql defaults for starting up server? I see default unix socket file is /tmp/mysql.sock which could be removed by someone accidently. Is it normal to keep it that way or keep in a protected directory ? Thanks. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How can I find this data?
[snip] I have four tables among others in my database: Bookings, Work_Types, Practices Projects. Bookings occur in a Practice for a Project and have a Work_Type. A Practice can have many Bookings but must have one and only one Booking where the Work_Type.Day_Type = 1. This rule was introduced after the system was initially set up and I have a feeling there may be Practices that have no Day 1's. So how can I perform a query that returns all practices that have had bookings but no Day 1's for a particualar project? Please see table definitions below. [/snip] Thanks very much for your help. mysql desc Bookings; | Booking_ID | int(11) | | PRI | | Booking_Type| varchar(15) | | | | User_ID | int(11) | | | 0 | Project_ID | int(11) | YES | | | Rep_ID | int(11) | YES | | | Practice_ID | int(11) | YES | | | Booking_Creator_ID | int(11) | YES | | | Booking_Creation_Date | datetime| YES | | | Booking_Start_Date | datetime| | | | Booking_End_Date| datetime| | | | Booking_Completion_Date | date| YES | | | Booking_Mileage | int(5) | YES | | | Booking_Status | varchar(15) | | | | Unavailability_ID | int(2) | YES | | | Task_ID | int(11) | YES | | | Work_Type_ID| int(2) | YES | | | Additional_Notes| text| YES | | | Pre_Event_Copy_Received_By_Scheduling | char(3) | YES | | | Post_Event_Original_Completed_Form_Received | char(3) | YES | | | Section_C | char(3) | YES | | | Date_Difference | varchar(20) | | | | AU_Booking_ID | int(11) | YES | | mysql desc Projects; | Project_ID | int(11) | | PRI | NULL| | Project_Name | varchar(100) | | | | | Client_ID | int(11) | | | 0 | | Rep_Viewable | char(3) | | | Yes | | Administrator_ID | int(11) | YES | | NULL| | Administrator_Phone_Number | varchar(20) | | | | | Project_Manager_ID_1 | int(11) | YES | | NULL| | Project_Manager_ID_2 | int(11) | YES | | NULL| mysql desc Practices; | Practice_ID| int(11) | | PRI | NULL| auto_increment | PCT_ID | int(11) | | | 0 | | Practice_Name | varchar(40) | | | | | Practice_Address | varchar(255) | | | | | Practice_Postcode | varchar(10) | | | | | Practice_Telephone | varchar(15) | | | | | Practice_Manager | varchar(40) | | | | | Practice_Lead_GP | varchar(40) | | | | | Practice_List_Size | int(11) | YES | | NULL| | Practice_System| varchar(100) | | | | | NHS_ID | varchar(20) | YES | | NULL| | MiQuest| char(3) | YES | | NULL| mysql desc Work_Types; | Work_Type_ID | int(3) | | PRI | NULL| auto_increment | | Project_ID | int(11) | YES | | NULL|| | Day_Type | int(2) | YES | | NULL|| | Work_Type| varchar(40) | | | || Not including all the possible columns, just an untested skeletonand pure speculation without seeing a proposed result set SELECT p.Practice_ID, w.Day_Type FROM Practices p LEFT OUTER JOIN Bookings b ON(p.Practice_ID = b.Practice_ID) LEFT OUTER JOIN WorkTypes w ON(b.Project_ID = w.Project_ID) WHERE b.Practice_ID IS NOT NULL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
High Load testing
Hi, I used sql-bench, but that is testing things we already know. We want to establish how many concurrent connections / queries our database server can handle before it starts getting into trouble (no, a different one from the email of last night). This system is a dual proc with 4GB ram and over 500GB on a RAID 5 setup I found super-smack (which tests 100% what I want), but the problem now is that it does not compile on FreeBSD, and the few (literally not even 3) linux boxes we have cannot handle the load to stress our DB server to the max. This I know because iostat on the FreeBSD DB Server shows that the entire box is virtually idle (the load averages confirms) while the tests on super-smack run. The worse I got was a load of 0.5 on the DB server with close to 800 threads running Any ideas please??? -- Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Slow queries, why?
Hi, I have an interesting problem, i.e upto 20k data is inserted in 20 min. But for 39k it took 3.5 hours. Could you please help me in this, what are all the possible scenarios which leads to this kind of problems. Is there any fine tuning mechanism in Mysql 4.0.23 with innodb? Please help me in this, it is very urgent. Thanks, Narasimha -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 04, 2005 1:11 PM To: mysql@lists.mysql.com Subject: Re: Slow queries, why? Hello. We're running MySQL 4.11 on a machine with 2GB memory, the table is InnoDB with a compound primary key, and additional indexes on all rows with searchable options in the API. Any generic advice or admin tools would be great. Use EXPLAIN to determine how efficient your indexes are. Using a lot of keys could slow down the INSERT operations but fasten the SELECTs. InnoDB monitors might be helpful in your case as well. See: http://dev.mysql.com/doc/mysql/en/explain.html http://dev.mysql.com/doc/mysql/en/innodb-monitor.html Joseph Cochran [EMAIL PROTECTED] wrote: So here's my situation: we have a database that has a table of about 5 million rows. To put a new row into the table, I do an INSERT ... SELECT, pulling data from one row in the table to seed the data for the new row. When there are no active connections to the DB other than the one making the INSERT, it runs like a charm. But during normal daytime operation, when we run around 50 connections (most sleeping at any one time), it takes up to two minutes to do, and ends up locking any other inserts or updates against that table for the entire time. I'll get into more specifics if they're required, but I wanted to ask in general if MySQL has tools to diagnose this, or if anyone has had general situations like this. In SQL Server (which is where I have most of my experience) I could use the trace tool and the Query Analyzer to tell what the execution plan for the query was and thus what's stalling it (an index gone bad, a weird locking situation, etc). We're running MySQL 4.11 on a machine with 2GB memory, the table is InnoDB with a compound primary key, and additional indexes on all rows with searchable options in the API. Any generic advice or admin tools would be great. -- Joe -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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] Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: High Load testing
From: Chris Knipe I found super-smack (which tests 100% what I want), but the problem now is Maybe you can use Jeremy Zawodny's MyBench? http://jeremy.zawodny.com/mysql/mybench/ It requires a bit of programming skills to get your logic in the Perl script (plus DBI, DBD::mysql and Time::HiRes installed), but you probably have Perl running on the FreeBSD machine anyway... Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to check if keys disabled?
You can use the mysqlshow command to list all disabled keys: e.g.: mysqlshow -k world foo When the indexes are displayed the word disabled will be in the comment field -- Partha Dutta, Senior Consultant MySQL Inc, NY, USA, www.mysql.com Are you MySQL certified? www.mysql.com/certification Victor Pendleton wrote: Try show index from t1; Show index from t2; Hi Victor, Nope, this does not help. t1 has key disabled, t1 enabled, and the result is the same: mysql show index from t1\G *** 1. row *** Table: t1 Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: x Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: 1 row in set (0.00 sec) mysql show index from t2\G *** 1. row *** Table: t2 Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: x Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: 1 row in set (0.00 sec) cheers, Jacek -Original Message- From: Jacek Becla [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 03, 2005 4:00 PM To: Jay Blanchard Cc: [EMAIL PROTECTED] Subject: Re: how to check if keys disabled? Jay Are you sure? DESCRIBE tells me the table has an index, but not whether the index is enabled or not: mysql create table t1 (x int primary key); Query OK, 0 rows affected (0.01 sec) mysql create table t2 (x int primary key); Query OK, 0 rows affected (0.01 sec) mysql alter table t1 disable keys; Query OK, 0 rows affected (0.00 sec) mysql describe t1; +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | x | int(11) | NO | PRI | | | +---+-+--+-+-+---+ 1 row in set (0.00 sec) mysql describe t2; +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | x | int(11) | NO | PRI | | | +---+-+--+-+-+---+ 1 row in set (0.00 sec) Am I missing something? thanks, Jacek Jay Blanchard wrote: [snip] How can I find out if keys are enabled/disabled for a given table? Suppose I do: create table t1 (x int primary key); create table t2 (x int primary key); alter table t1 disable keys; How can I now find out that t1 has keys disabled, and t2 enabled? [/snip] DESCRIBE t1 or DESCRIBE t2
Underline or minus sign ?
I'm going to start the InnoDB engine... and I want to know if the syntax for the CNF file unified at last ? Can I use: innodb-file-per-table instead of innodb_file_per_table ? [This is just an example] Can I use only minus sign in the whole CNF file instead of underline ? Gabriel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Fwd: Re: Collation problems or messed joins?]
Hi! thanks for your response. This are the values of the variables you asked for: collation_connection: utf8_general_ci collation_database: utf8_general_ci collation_server: utf8_general_ci character_set_client: utf8 character_set_connection: utf8 character_set_database: utf8 character_set_results: NULL character_set_server: utf8 character_set_system: utf8 These were taken from a query inside the app. From the query browser the only difference is: character_set_results: utf8 Anyway, the same query has the same results in the app and the query browser. The app is using the .net connector Thanks Andrés Villanueva Gleb Paharenko wrote: Hello. Please, send the output of the following statements: show variables like '%colla%'; show variables like '%char%'; Andr$s Villanueva [EMAIL PROTECTED] wrote: Anyone?? Original Message The tables are now entirely in utf8, and that is also the instance's default. I'm using the .net dll to connect and the connection string has charset = utf8. I'm testing everything with the query browser, and i get the exact same behaviour. Again, if anyone wants a script to recreate the tables with some data, I can send it to you instantly. Thanks Andr$s Villanueva Kevin Cowley wrote: Have you check that the collation for the text columns match the collation for the table and that you've set UTF8 for the query (set char set utf8). Kevin Cowley Product Development Alchemetrics Ltd SMARTER DATA , FASTER Tel: 0118 902 9000 (swithcboard) Tel: 0118 902 9099 (direct) Web: www.alchemetrics.co.uk Email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can I find this data?
shaun thornburgh [EMAIL PROTECTED] wrote on 05/04/2005 06:54:23 AM: Hi, I have four tables among others in my database: Bookings, Work_Types, Practices Projects. Bookings occur in a Practice for a Project and have a Work_Type. A Practice can have many Bookings but must have one and only one Booking where the Work_Type.Day_Type = 1. This rule was introduced after the system was initially set up and I have a feeling there may be Practices that have no Day 1's. So how can I perform a query that returns all practices that have had bookings but no Day 1's for a particualar project? Please see table definitions below. Thanks very much for your help. mysql desc Bookings; +-+-+-- +-+-++ | Field | Type| Null | Key | Default | Extra | +-+-+-- +-+-++ | Booking_ID | int(11) | | PRI | NULL| auto_increment | | Booking_Type| varchar(15) | | | Unavailability || | User_ID | int(11) | | | 0 || | Project_ID | int(11) | YES | | NULL|| | Rep_ID | int(11) | YES | | NULL|| | Practice_ID | int(11) | YES | | NULL|| | Booking_Creator_ID | int(11) | YES | | NULL|| | Booking_Creation_Date | datetime| YES | | NULL|| | Booking_Start_Date | datetime| | | -00-00 00:00:00 || | Booking_End_Date| datetime| | | -00-00 00:00:00 || | Booking_Completion_Date | date| YES | | NULL|| | Booking_Mileage | int(5) | YES | | NULL|| | Booking_Status | varchar(15) | | | Other || | Unavailability_ID | int(2) | YES | | NULL|| | Task_ID | int(11) | YES | | NULL|| | Work_Type_ID| int(2) | YES | | NULL|| | Additional_Notes| text| YES | | NULL|| | Pre_Event_Copy_Received_By_Scheduling | char(3) | YES | | NULL|| | Post_Event_Original_Completed_Form_Received | char(3) | YES | | NULL|| | Section_C | char(3) | YES | | NULL|| | Date_Difference | varchar(20) | | | n/a || | AU_Booking_ID | int(11) | YES | | NULL|| +-+-+-- +-+-++ 22 rows in set (0.00 sec) mysql desc Projects; ++--+--+-+- ++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+- ++ | Project_ID | int(11) | | PRI | NULL| auto_increment | | Project_Name | varchar(100) | | | | | | Client_ID | int(11) | | | 0 | | | Rep_Viewable | char(3) | | | Yes | | | Administrator_ID | int(11) | YES | | NULL| | | Administrator_Phone_Number | varchar(20) | | | | | | Project_Manager_ID_1 | int(11) | YES | | NULL| | | Project_Manager_ID_2 | int(11) | YES | | NULL| | ++--+--+-+- ++ 8 rows in set (0.00 sec) mysql desc Practices; ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra |
RE: How can I find this data?
Hi Jay, Thanks for your reply, I tried your query but it jus hang :( Here is some sample data: mysql SELECT * FROM Bookings WHERE Practice_ID = 11049; ++--+-+++-++---+-+-+-+-++---+-+--+--+---+-+---+-+---+ | Booking_ID | Booking_Type | User_ID | Project_ID | Rep_ID | Practice_ID | Booking_Creator_ID | Booking_Creation_Date | Booking_Start_Date | Booking_End_Date| Booking_Completion_Date | Booking_Mileage | Booking_Status | Unavailability_ID | Task_ID | Work_Type_ID | Additional_Notes | Pre_Event_Copy_Received_By_Scheduling | Post_Event_Original_Completed_Form_Received | Section_C | Date_Difference | AU_Booking_ID | ++--+-+++-++---+-+-+-+-++---+-+--+--+---+-+---+-+---+ | 6148 | Booking |1571 | 32 | 1629 | 11049 | 75 | 2005-03-11 13:29:40 | 2005-04-01 09:30:00 | 2005-04-01 17:30:00 | NULL| 0 | Incomplete | NULL | 26 | 76 | x| Yes | NULL | No| n/a | NULL | | 6149 | Booking |1571 | 32 | 1629 | 11049 | 75 | 2005-03-11 13:30:49 | 2005-05-06 09:30:00 | 2005-05-06 17:30:00 | NULL|NULL | Incomplete | NULL | 26 | 77 | x| 0 | NULL | 0 | n/a | NULL | | 50 | Booking |1571 | 3 | 1629 | 11049 | 75 | 2005-03-11 13:31:20 | 2005-05-09 09:30:00 | 2005-05-09 17:30:00 | NULL|NULL | Incomplete | NULL | 26 |9 | x| 0 | NULL | 0 | n/a | NULL | | 55 | Booking |1645 | 3 | 1629 | 11049 | 75 | 2005-04-01 11:38:24 | 2005-04-04 09:30:00 | 2005-04-04 17:30:00 | NULL| 0 | Incomplete | NULL | 26 |9 | x| No | NULL | No| n/a | NULL | ++--+-+++-++---+-+-+-+-++---+-+--+--+---+-+---+-+---+ 4 rows in set (0.02 sec) mysql SELECT * FROM Work_Types WHERE Project_ID = 32; +--++--++ | Work_Type_ID | Project_ID | Day_Type | Work_Type | +--++--++ | 76 | 32 |1 | Day 1 | | 77 | 32 |2 | Day 2 | +--++--++ 2 rows in set (0.00 sec) mysql SELECT * FROM Work_Types WHERE Project_ID = 3; +--++--++ | Work_Type_ID | Project_ID | Day_Type | Work_Type | +--++--++ |8 | 3 |1 | Day 1 | |9 | 3 |2 | Day 2 | +--++--++ 2 rows in set (0.00 sec) mysql Here you can see that Practice 11049 has four bookings. The two for project 32 are ok, but the two for project 3 do not have a booking where the day type is 1. I hope this makes sense! Thanks again for your help. Shaun From: Jay Blanchard [EMAIL PROTECTED] To: shaun thornburgh [EMAIL PROTECTED], mysql@lists.mysql.com Subject: RE: How can I find this data? Date: Wed, 4 May 2005 06:36:18 -0500 [snip] I have four tables among others in my database: Bookings, Work_Types, Practices Projects. Bookings occur
Re: Tables lost in new location of database
I am not sure about max - but I am running mysql.exe (I think it is the same as mysqld-nt). No - when I do a select * from on a table in the moved database - I get the error that the table does not exist. well - how can i tell if i have disabled symlinks??? I have not checked using 4.1.11. Thanks, Anoop On 5/4/05, Gleb Paharenko [EMAIL PROTECTED] wrote: Hello. Are you sure that you are running mysqld-max (mysqld-max-nt)? Are you able to select data from invisible tables? Is it possible that you have disabled symlinks? Does the problem remain with 4.1.11 version? I use mysql 4.0.23-nt on win XP. In order to shift some of my databases I followed the following steps as per a recent post: 1. I moved the folders containing the database files that I wanted to move to a new folder (from data to data2) after stopping mysql of course. 2. In the data folder I created a file named with the database (same as the folder names that I moved) prefixed with .sym. 3. Inside each of these .sym files I enterred (typed) in the exact path of the new folders and nothing else. 4. Restarted mysql Now when I give show databases; at the mysql prompt, all my database names are displayed including the ones I moved. Here is the issue: When I use one of the databases that I had moved and give: show tables I am getting an empty set. I know the data is there because the size of the .myd files is 50 MB - well none of the .myi or .frm file is missing or empty! Also I noticed that the .sym files have numbers auto-enterred before the file path I had specified - I guess it is in running mode. So how do I see my tables on the moved databases and query on them?? Thanks in advance, Anoop Anoop kumar V [EMAIL PROTECTED] wrote: -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET http://Ensita.NET ___/ www.mysql.com http://www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Thanks and best regards, Anoop
Re: MyISAM error 127
Hi, Thanks for the reply. Are u suggesting that I shutdown MySQL? I thought that a FLUSH table command would take care of things. Stupid me! gerald_clark [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] wrote: Hi, We're in the process of benchmarking/evaluating MySQL(4.1.9) on Linux for our data warehouse. I have a group of tables (9/2004 through 2/2005) that average about 95 million rows (215 byte rows). Using PERL and piping to mysql LOAD DATA is the way we've been loading. That all worked as advertized when single threaded. Once all the data was loaded one of the tables had several indexes created. Again, no problem. Then we tried running myisampack against one of the tables. It worked against the table, however other tables somehow got corrupted. Running a query against the table produced the following error: MyISAM Engine returned error 127 Ok, so we ran myisamchk --quick --recover It ran successfully (or so we thought). Query the table for rows and the number comes back. Query the data get same error message. Next, we ran myisamchk --force --extended-check It ran successfully (or so we thought). Query the table for rows and the number comes back. Query the data get same error message. Then decide to truncate the table. Same error. Dropped the table. Same error. Recycled MySQL then dropped the table. It worked. Any ideas as to what may be going on here. George You did not state that you stopped the server before running myisamchk. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] __ Switch to Netscape Internet Service. As low as $9.95 a month -- Sign up today at http://isp.netscape.com/register Netscape. Just the Net You Need. New! Netscape Toolbar for Internet Explorer Search from anywhere on the Web and block those annoying pop-ups. Download now at http://channels.netscape.com/ns/search/install.jsp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can't create interrupt-thread (error 11, errno: 0)
hi ,guys. i tried to start mysqld it reports below ,can someone give me some advices? 050504 09:43:33 mysqld started 050504 9:43:33 [ERROR] Can't create interrupt-thread (error 11, errno: 0) 050504 09:43:33 mysqld ended Jack [EMAIL PROTECTED] 2005-05-04
ODBC on UNIX and NFS
Hello All I am about to set up ODBC for MySQL (iODBC) on our UNIX systems. We have many machines that will need to use this installation. I would like to leverage a NFS mount that is available to all the machines. We currently leverage the NFS mount for a MySQL Client. I am wondering if anyone has this type of implementation or any comments? Thanks - Gabe There are no problems, only solutions. Gabe Tucker Bloomberg LP (609) 750 6668 - P (646) 268 5681 - F -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FW: ODBC on UNIX and NFS
Actually - I have some more details to make this clearer: We will be using UnixODBC - already installed on a NFS mount, and the MySQL drivers. So, could we install the MySQL drivers on the NFS Mount? Thanks again - Gabe -Original Message- From: [EMAIL PROTECTED] Sent: Wednesday, May 04, 2005 10:30 AM To: Mysql General (E-mail) Subject: ODBC on UNIX and NFS Hello All I am about to set up ODBC for MySQL (iODBC) on our UNIX systems. We have many machines that will need to use this installation. I would like to leverage a NFS mount that is available to all the machines. We currently leverage the NFS mount for a MySQL Client. I am wondering if anyone has this type of implementation or any comments? Thanks - Gabe There are no problems, only solutions. Gabe Tucker Bloomberg LP (609) 750 6668 - P (646) 268 5681 - F -- 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]
ACCESS ODBC Interface whit 5.0.4
I have been using ACCESS to do simpe data editing on our MySQL tables for 3 years. I recently installed 5.0.4 on my machine to evaluate it. I linked the tables into ACCESS through my old ODBC driver and got ODBC-update on a linked table 'product_order_choice' failed [Microsoft][ODBC Driver Manager] SQL data type out of rance (#0) I then downloaded and installed the current ODBC connector [3.51.11] thinking maybe it was my old ODBC copy, but get the same result. Has anyone else seen this or have any ideas?
Re: Foreign Key Restriction
Oliver Hirschi [EMAIL PROTECTED] schrieb im Newsbeitrag news:[EMAIL PROTECTED] Hi I updated mySQL 4.0.8 to 4.1.1 and I have now problems with foreign key retrictions. Is it right, that mySQL 4.1.x has something changed due to the foreign key restriction? Is there an option to turn off the foreign key restriction in mySQL 4.1.1? I found the mistake. There was an INSERT and a foreign-key was setted to the value 0. It seems, this was possible with mySQL version 4.0.8, but not anymore in version 4.1.x. Does anybody known something about that? Thanks, Oliver Hirschi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Foreign Key Restriction
You can turn off foreign key restrictions within your session: SET SESSION foreign_key_checks = 0; Then later, turn them back on using SET SESSION foreign_key_checks = 1; -- Partha Dutta, Senior Consultant MySQL Inc, NY, USA, www.mysql.com Are you MySQL certified? www.mysql.com/certification -Original Message- From: news [mailto:[EMAIL PROTECTED] On Behalf Of Oliver Hirschi Sent: Wednesday, May 04, 2005 10:50 AM To: mysql@lists.mysql.com Subject: Re: Foreign Key Restriction Oliver Hirschi [EMAIL PROTECTED] schrieb im Newsbeitrag news:[EMAIL PROTECTED] Hi I updated mySQL 4.0.8 to 4.1.1 and I have now problems with foreign key retrictions. Is it right, that mySQL 4.1.x has something changed due to the foreign key restriction? Is there an option to turn off the foreign key restriction in mySQL 4.1.1? I found the mistake. There was an INSERT and a foreign-key was setted to the value 0. It seems, this was possible with mySQL version 4.0.8, but not anymore in version 4.1.x. Does anybody known something about that? Thanks, Oliver Hirschi -- 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]
Suggestions to retrieve surviving data
Hello all, I have a problem on a development machine that somehow got every single mysql program in the /usr/bin directory wiped out (which looks like it was everthing but mysqld). The server is actually up and running, and the datadir is intact, so it looks like my data is okay. However, I had this set up only with users from localhost, so I can't connect from a client on another machine. It's like I'm trying to reach an island but I don't have a boat. I have an older backup, but would like to try and save the current data. It's not at all critical, so I don't want to go to too much bother - it's just a convenience thing. I only have 4 important MYISAM tables, so my plan is: 1. Shut down server 2. Back up datadir 3. Install MySQL-server and MySQL-client 4. Restore backup to datadir I'm installing same version over the old from RPM, and planning to just -force the install. My second thought was I could try just forcing the install of client and mysqldump-ing the data before reinstalling the server, which I'm thinking is probably the better way to go. Am I missing any issues or a better way to do this? Thanks, kgt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: perl/mysql issue...
kristen...
the issues that i saw/reolved (at least for now) have to do with how mysql
sets things up for replication, which has to do with granting the correct
privs/rights...
i did the following:
1) create the database (foo)
2) grant all privs to user for foo on host
3) grant replication slave, replication client on *.* to host/user
create database livejournal
GRANT ALL PRIVILEGES ON livejournal.* TO 'lj'@'192.168.1.55' IDENTIFIED BY
'ljpass';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO lj@192.168.1.55
IDENTIFIED BY 'ljpass';
i also had to remember to start mysqld --old-passwords to allow my perl/php
apps to communicate with the mysql4.1 db
i can now do the 'show slave status' and access the db from perl...
this seems to have allowed me to get past this hurdle...
-bruce
-Original Message-
From: Kristen G. Thorson [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 04, 2005 8:21 AM
To: [EMAIL PROTECTED]
Subject: Re: perl/mysql issue...
Bruce,
You can't issue SHOW SLAVE STATUS because you only have permissions on
the livejournal database. Try this: as root or someone with sufficient
privileges,
GRANT USAGE on *.* TO [EMAIL PROTECTED] IDENTIFIED BY 'ljpass';
(Usage basically only allows login. You're not granting this user
anything else, except to ability to issue
USE livejournal;
kgt
bruce wrote:
hey kristen...
the issue is a mysql/privs/rights issue. i get the err msg when i simply do
the 'mysql -ulj -h192.168.1.55 -p' and then after entering the passwd, if i
do a 'mysqlshow slave status' i get the err msg
so it definitely is something that's realted to mysql/tables/privs/access
setup...
i'm not currently knowledgable enough regarding mysql to laser in on the
issue...
-bruce
-Original Message-
From: Kristen G. Thorson [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 03, 2005 1:45 PM
To: [EMAIL PROTECTED]
Subject: Re: perl/mysql issue...
My apologies, Bruce. I normally add users manually (INSERT INTO
mysql.Users...) so I have to use the PASSWORD() function. It is not
necessary with a GRANT statement. And the reason CURRENT_USER() didn't
work
is because it is not available until version 4.0.
Okay, then next focus on the error message.
Access denied for user: '[EMAIL PROTECTED]' (Using password: NO)
It's correctly displaying your username and host, but saying you are not
using a password. You should get this error if you try to log in via
command line and don't include a password. Your user name and password are
correctly set up, since you didn't make the 'error' I pointed out with the
GRANT statement, and you can log in via command line. This means it's
probably in your code somewhere. I'm not a perl person, so I'm not going
to
be able to help you much beyond this point, but try to echo the values you
pass into the DBI connect:
$dbh = DBI-connect($dsn, $user, $pass, {
PrintError = 1,
AutoCommit = 1,});
Perhaps your password is not getting passed in. Are you sure $pass
contains
a value at this line?
kgt
bruce wrote:
kristen..
i made a typo.. i've been using 192.168.1.55 but i accidentally typed
localhost below.. everything i'm doing/have done has been 192.168.1.55.
i tried to enter your grant action:
mysql GRANT ALL PRIVILEGES ON livejournal.* TO
lj@'192.168.1.55' IDENTIFIED BY PASSWORD('ljpass');
and mysql threw a syntax error regarding the 'PASSWORD/password' attribute
the same thing happened when i tried 'select current_user();
i'm running mysql 3.28.53
any other ideas/thoughts...
-bruce
-Original Message-
From: Kristen G. Thorson [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 03, 2005 12:05 PM
To: [EMAIL PROTECTED]
Subject: Re: perl/mysql issue...
mysql GRANT ALL PRIVILEGES ON livejournal.* TO
lj@'localhost' IDENTIFIED BY 'ljpass';
Should be:
mysql GRANT ALL PRIVILEGES ON livejournal.* TO
lj@'localhost' IDENTIFIED BY PASSWORD('ljpass');
And
i can access the mysql db/livejournal from the mysql client/command line.
mysql -ulj -host192.168.1.55 -p
When you log in, issue SELECT CURRENT_USER(); This will tell you who
exactly you are logged in as. Sometimes, you are not logged in as the
user you think. You created user [EMAIL PROTECTED], but are logging in as
[EMAIL PROTECTED] These are different users. If 192.168.1.55 is not the
computer where mysql db resides, then you need to create a user account
[EMAIL PROTECTED] or change your connect string to host=localhost.
HTH,
kgt
bruce wrote:
hi...
i have the following issue.. i've researched it from google.. but i still
can't quite figure it out...
i'm using a test app with DBI-connect() and i'm getting the following
response... (print/debug statements...)
--
'bd dsn =
DBI:mysql:livejournal;host=192.168.1.55;port=3306;mysql_connect_timeout=2
'bd user = lj
'bd passwd = ljpass
'bd fdsn1 =
Re: ERROR 1251: Client does not support authentication protocol requested by server; consider upgrading MySQL client
Hassan Schroeder wrote: Mark Sargent wrote: h, that is annoying, as I did a yum remove mysql b4 installing 4.1. Shouldn't the yum remove, remove it fully..? Sorry, can't help there, don't know anything about 'yum'. [EMAIL PROTECTED] ~]# mysql -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 to server version: 4.1.11-standard and, if it's the older 1, why does it state ver 4.1.11-standard, if it's the original..? Which showed that mysql is also at /usr/bin/mysql, the older, I guess, that you alluded to, so, when typing mysql -p it is using the older client to connect to ver 4.1.11-standard which is at /usr/local/mysql, yes..? Sorry, I'm still rather a newb at Linux also. `mysql` is the client, which as you surmise is the old version; the MySQL daemon (server) process is `mysqld`, frequently started using a script called `mysqld_safe` or `safe_mysqld` ; look in the 'bin' directory of your MySQL install... You may also find it useful to run something like: prompt# find / -type f -name 'mysql*' -print :: to see what-all's scattered around your system :-) HTH! Hi All, ok, I don't fully understand why, but, mysql is still installed in /usr/bin. What I've done, just for now, is renamed mysql in that dir to mysqlold. I've added /usr/local/mysql/bin to my path. I then tried the cmd mysql and also ./mysql from within the dir /usr/local/mysql/bin, and after entering the correct password, I get the following, [EMAIL PROTECTED] bin]# ./mysql -p Enter password: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) If I run ./usr/bin/mysqlold I can connect. What am I not understanding with this..? Cheers, again. Mark Sargent. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
select's with various levels
Dear all at mysql list I'dd like to know if it is possible (and the right sintax) to execute a nested (2 or more levels) select... something like this. select * from products where productId IN ( select idNumber from other_table ); This has the same result than a JOIN. I've done this some years ago in Oracle and I would liki to know if it is possible in mysql. Thanks joao __ Sabe quanto gasta com a sua ligação à Internet? Verifique aqui: http://acesso.portugalmail.pt/contas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select's with various levels
It's possible starting with mysql 4.1 -Eric [EMAIL PROTECTED] wrote: Dear all at mysql list I'dd like to know if it is possible (and the right sintax) to execute a nested (2 or more levels) select... something like this. select * from products where productId IN ( select idNumber from other_table ); This has the same result than a JOIN. I've done this some years ago in Oracle and I would liki to know if it is possible in mysql. Thanks joao __ Sabe quanto gasta com a sua ligação à Internet? Verifique aqui: http://acesso.portugalmail.pt/contas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ERROR 1251: Client does not support authentication protocol requested by server; consider upgrading MySQL client
Mark Sargent wrote: ok, I don't fully understand why, but, mysql is still installed in /usr/bin. What I've done, just for now, is renamed mysql in that dir to mysqlold. I've added /usr/local/mysql/bin to my path. I then tried the cmd mysql and also ./mysql from within the dir /usr/local/mysql/bin, and after entering the correct password, I get the following, [EMAIL PROTECTED] bin]# ./mysql -p Enter password: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) If I run ./usr/bin/mysqlold I can connect. What am I not understanding with this..? If it were me, I'd remove all traces of mysql* anything from the system and start fresh. :-) But in any case, which mysqld process is now running? I'm guessing it's the old one. If so, kill it, and start up your new version using an unambiguous path (e.g., /usr/local/mysql/bin/mysqld_safe). Then try to connect with your new client. HTH, -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tables lost in new location of database
Is there a link that someone can suggest which details out how to move databases from one location to another in mysql? Thanks in advance. Anoop On 5/4/05, Anoop kumar V [EMAIL PROTECTED] wrote: I am not sure about max - but I am running mysql.exe (I think it is the same as mysqld-nt). No - when I do a select * from on a table in the moved database - I get the error that the table does not exist. well - how can i tell if i have disabled symlinks??? I have not checked using 4.1.11. Thanks, Anoop On 5/4/05, Gleb Paharenko [EMAIL PROTECTED] wrote: Hello. Are you sure that you are running mysqld-max (mysqld-max-nt)? Are you able to select data from invisible tables? Is it possible that you have disabled symlinks? Does the problem remain with 4.1.11 version? I use mysql 4.0.23-nt on win XP. In order to shift some of my databases I followed the following steps as per a recent post: 1. I moved the folders containing the database files that I wanted to move to a new folder (from data to data2) after stopping mysql of course. 2. In the data folder I created a file named with the database (same as the folder names that I moved) prefixed with .sym. 3. Inside each of these .sym files I enterred (typed) in the exact path of the new folders and nothing else. 4. Restarted mysql Now when I give show databases; at the mysql prompt, all my database names are displayed including the ones I moved. Here is the issue: When I use one of the databases that I had moved and give: show tables I am getting an empty set. I know the data is there because the size of the .myd files is 50 MB - well none of the .myi or .frm file is missing or empty! Also I noticed that the .sym files have numbers auto-enterred before the file path I had specified - I guess it is in running mode. So how do I see my tables on the moved databases and query on them?? Thanks in advance, Anoop Anoop kumar V [EMAIL PROTECTED] wrote: -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET http://Ensita.NET ___/ www.mysql.com http://www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Thanks and best regards, Anoop -- Thanks and best regards, Anoop
Re: Slow queries, why?
Yes, indexes slow down inserts (or updates that change the value of a column that is indexed). Also, remember that MySQL only uses one index per per table in a query. So if there are some columns in your table that are indexed, but, 1) Have poor cardinality (number of distinct values - low cardinality means there aren't many distinct values) 2) Are only used in a where clause with another column that has good cardinality then they are an excellent candidate for removal. While EXPLAIN is great for queries, it won't help much with an insert; it might be useful for figuring out what indexes are used, and which ones aren't. Use show innodb status to get an idea of what's going on (Gleb suggested it in the link to the innodb monitor). You should also post the relevant parts of your my.cnf file; have you seen this equation before: Memory Used By MySQL = Innodb_buffer_pool_size + key_buffer_size + max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size) + max_connections*2MB Use it to calculate how much memory you are using. Finally, read up on phantom reads: http://sunsite.mff.cuni.cz/MIRRORS/ftp.mysql.com/doc/en/InnoDB_Next-key_locking.html This might be what's happening. David Gleb Paharenko wrote: Hello. We're running MySQL 4.11 on a machine with 2GB memory, the table is InnoDB with a compound primary key, and additional indexes on all rows with searchable options in the API. Any generic advice or admin tools would be great. Use EXPLAIN to determine how efficient your indexes are. Using a lot of keys could slow down the INSERT operations but fasten the SELECTs. InnoDB monitors might be helpful in your case as well. See: http://dev.mysql.com/doc/mysql/en/explain.html http://dev.mysql.com/doc/mysql/en/innodb-monitor.html Joseph Cochran [EMAIL PROTECTED] wrote: So here's my situation: we have a database that has a table of about 5 million rows. To put a new row into the table, I do an INSERT ... SELECT, pulling data from one row in the table to seed the data for the new row. When there are no active connections to the DB other than the one making the INSERT, it runs like a charm. But during normal daytime operation, when we run around 50 connections (most sleeping at any one time), it takes up to two minutes to do, and ends up locking any other inserts or updates against that table for the entire time. I'll get into more specifics if they're required, but I wanted to ask in general if MySQL has tools to diagnose this, or if anyone has had general situations like this. In SQL Server (which is where I have most of my experience) I could use the trace tool and the Query Analyzer to tell what the execution plan for the query was and thus what's stalling it (an index gone bad, a weird locking situation, etc). We're running MySQL 4.11 on a machine with 2GB memory, the table is InnoDB with a compound primary key, and additional indexes on all rows with searchable options in the API. Any generic advice or admin tools would be great. -- Joe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
automatic generation of table creation code
Hello all at mysql, Are there tools available to automatically generate database (tables) creation code starting from some grafical description of data... maybe it's a crazy idea but I think it makes sense... maybe: ER Diagrams to SQL or:Object Diagrams to SQL anything based on some kind of visual data description/specification. Thank you jmf __ Email gratuito com 2 000 MB Espaço para guardar 20 anos de correio http://www.portugalmail.pt/2000mb -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INSERT DATE using USA Date Format
I have some data where the date is already in USA format (MM-DD-).
I would like to be able to insert this data without having to massage
the date in the code.
Is there a way to specify the Date Format for an INSERT statement?
Something like:
INSERT INTO tblMyTable(myDate) VALUES(DATE_FORMAT('USA', '12-31-2004');
Thanks,
MIke
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT DATE using USA Date Format
In the last episode (May 04), Michael J. Pawlowsky said:
I have some data where the date is already in USA format
(MM-DD-). I would like to be able to insert this data without
having to massage the date in the code.
MySQL seems to think that USA format is MM.DD., which I've never
seen before :)
Is there a way to specify the Date Format for an INSERT statement?
Something like:
INSERT INTO tblMyTable(myDate) VALUES(DATE_FORMAT('USA', '12-31-2004');
Try the STR_TO_DATE function:
SELECT STR_TO_DATE('12-31-2004','%m-%d-%Y');
2004-12-31
--
Dan Nelson
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow queries, why?
Thanks! Explain and InnoDB monitor were exactly what I needed to diagnose and fix the problem! In case you were curious, the issue was that the statement I was expecting to run was not the statement that was running, but the first hundred and some-odd characters in both were the same. Using the monitor I was able to see that the wrong thing was running. Some SELECTs are still taking longer than they should, but I have some new tools at my disposal, which makes me very happy. -- Joe On 5/4/05, Gleb Paharenko [EMAIL PROTECTED] wrote: Hello. We're running MySQL 4.11 on a machine with 2GB memory, the table is InnoDB with a compound primary key, and additional indexes on all rows with searchable options in the API. Any generic advice or admin tools would be great. Use EXPLAIN to determine how efficient your indexes are. Using a lot of keys could slow down the INSERT operations but fasten the SELECTs. InnoDB monitors might be helpful in your case as well. See: http://dev.mysql.com/doc/mysql/en/explain.html http://dev.mysql.com/doc/mysql/en/innodb-monitor.html Joseph Cochran [EMAIL PROTECTED] wrote: So here's my situation: we have a database that has a table of about 5 million rows. To put a new row into the table, I do an INSERT ... SELECT, pulling data from one row in the table to seed the data for the new row. When there are no active connections to the DB other than the one making the INSERT, it runs like a charm. But during normal daytime operation, when we run around 50 connections (most sleeping at any one time), it takes up to two minutes to do, and ends up locking any other inserts or updates against that table for the entire time. I'll get into more specifics if they're required, but I wanted to ask in general if MySQL has tools to diagnose this, or if anyone has had general situations like this. In SQL Server (which is where I have most of my experience) I could use the trace tool and the Query Analyzer to tell what the execution plan for the query was and thus what's stalling it (an index gone bad, a weird locking situation, etc). We're running MySQL 4.11 on a machine with 2GB memory, the table is InnoDB with a compound primary key, and additional indexes on all rows with searchable options in the API. Any generic advice or admin tools would be great. -- Joe -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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: INSERT DATE using USA Date Format SOLUTION
Dan Nelson wrote:
Is there a way to specify the Date Format for an INSERT statement?
Something like:
INSERT INTO tblMyTable(myDate) VALUES(DATE_FORMAT('USA', '12-31-2004');
Try the STR_TO_DATE function:
SELECT STR_TO_DATE('12-31-2004','%m-%d-%Y');
2004-12-31
Yup.. that works... Thanks.
INSERT INTO `test` (`mydate`) VALUES(STR_TO_DATE('12-31-2004', '%m-%d-%Y'));
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ACCESS ODBC Interface whit 5.0.4
Gordon wrote: I have been using ACCESS to do simpe data editing on our MySQL tables for 3 years. I recently installed 5.0.4 on my machine to evaluate it. I linked the tables into ACCESS through my old ODBC driver and got ODBC-update on a linked table 'product_order_choice' failed [Microsoft][ODBC Driver Manager] SQL data type out of rance (#0) I then downloaded and installed the current ODBC connector [3.51.11] thinking maybe it was my old ODBC copy, but get the same result. Has anyone else seen this or have any ideas? It would help if you posted details of the table / data you're working with. 'Data type out of range' usually means you've tried to put a numerical value in a field which is too small. For example, you may be trying to put an int value in a mediumint field. Or it could be that you're using a field type not supported by MS Access, such as an unsigned int or a bigint. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: automatic generation of table creation code
[EMAIL PROTECTED] wrote: Hello all at mysql, Are there tools available to automatically generate database (tables) creation code starting from some grafical description of data... maybe it's a crazy idea but I think it makes sense... maybe: ER Diagrams to SQL or:Object Diagrams to SQL anything based on some kind of visual data description/specification. Thank you jmf Data Architect by TheKompany does what you want: http://www.thekompany.com/products/dataarchitect/ Or at least I think it does. It's been quite some time since I used it. It uses QT, and is available on Linux, Windows and OS X. TheKompany is a pretty good company ... as far as companies go ... I have no problem recommending them. They're quite open-source friendly. Otherwise DBDesigner may or may not do what you want: http://www.fabforce.net/dbdesigner4/ I've only toyed with it briefly. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Unique Index on multiple columns that can contain NULL in MySQL
I'm getting lots of duplicate rows even though I have a unique index defined over multiple columns. The issue is that multiple NULL values are allowed, even when some values are not null. This could be as specified by the SQL standard, but it's certainly confusing for the developer. (By the way, one source of confusion is that phpMyEdit was disallowing duplicate values even though sql insert statements allowed them). Here's the test case: Goal: prevent duplicate rows on the (c1, c2) pair: CREATE TABLE `test_multi_column_null` ( `pk` INT NOT NULL AUTO_INCREMENT, `c1` VARCHAR( 30 ) , `c2` VARCHAR( 30 ) , PRIMARY KEY ( `pk` ) ); ALTER TABLE `test_multi_column_null` ADD UNIQUE `unique_index` ( `c1` , `c2` ); Unexpected works: INSERT INTO `test_multi_column_null` ( `pk` , `c1` , `c2` ) VALUES ( '', '1', NULL ), ( '', '1', NULL ); As expected, this causes a duplicate entry: INSERT INTO `test_multi_column_null` ( `pk` , `c1` , `c2` ) VALUES ( '', '1', '' ), ( '', '1', '' ); mysql select * from test_multi_column_null; ++--+--+ | pk | c1 | c2 | ++--+--+ | 1 | 1 | NULL | | 2 | 1 | NULL | | 3 | 1 | | ++--+--+ 3 rows in set (0.00 sec) Now, to drive the point home, let's add some null rows: INSERT INTO `test_multi_column_null` ( `pk` , `c1` , `c2` ) VALUES ( '', NULL , NULL ), ( '', NULL , NULL ); mysql select * from test_multi_column_null; ++--+--+ | pk | c1 | c2 | ++--+--+ | 1 | 1 | NULL | | 2 | 1 | NULL | | 3 | 1 | | | 4 | NULL | NULL | | 5 | NULL | NULL | ++--+--+ 5 rows in set (0.00 sec) Note: this works even with bdb engine in MySQL: mysql alter table test_multi_column_null engine = bdb; Query OK, 5 rows affected (0.03 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql select * from test_multi_column_null; ++--+--+ | pk | c1 | c2 | ++--+--+ | 1 | 1 | NULL | | 2 | 1 | NULL | | 3 | 1 | | | 4 | NULL | NULL | | 5 | NULL | NULL | ++--+--+ 5 rows in set (0.00 sec) Conclusion: if you want to enforce uniqueness, don't use columns that allow NULL.
Re: Unique Index on multiple columns that can contain NULL in MySQL
I think you should review the very recent thread why NOT NULL in PRIMARY key?? which might shed some light on your particular issue. In a nutshell, NULL!=NULL, so the database engine can not detect the duplicate rows, as is expected. -Hank On 5/4/05, Dennis Fogg [EMAIL PROTECTED] wrote: I'm getting lots of duplicate rows even though I have a unique index defined over multiple columns. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
stability of mysql error strings
I have a unique key on username and email address in mysql. In my application logic, I want to test for that response on failed insert. Error number 1062 tells me there is a duplicate key, but no way to tell what key that is. The error string returned is more telling, but seems less than ideal to use for stability and upgrade issues. It tells me username is key 2 and email is key 3. So, if error_string ends with 'key 2' etc etc I took care to lowercase the error string, so that's covered. What is not, is that it may not always 'ends with', so I could move to 'contains'. Anyway, I think you see what my concern is, wonder what the preferred method to fix is, or if someone can tell me when, if ever, the error string has ever changed format in mysql. I could run a insert ahead of time and parse the returned results, but I would rather not add the overhead of another select ahead of time. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: stability of mysql error strings
At 19:09 -0700 5/4/05, Scott Haneda wrote: I have a unique key on username and email address in mysql. In my application logic, I want to test for that response on failed insert. Error number 1062 tells me there is a duplicate key, but no way to tell what key that is. The error string returned is more telling, but seems less than ideal to use for stability and upgrade issues. It tells me username is key 2 and email is key 3. So, if error_string ends with 'key 2' etc etc I took care to lowercase the error string, so that's covered. What is not, is that it may not always 'ends with', so I could move to 'contains'. Anyway, I think you see what my concern is, wonder what the preferred method to fix is, or if someone can tell me when, if ever, the error string has ever changed format in mysql. Error strings do change format sometimes. And they won't necessarily be in English. :-) -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: stability of mysql error strings
on 5/4/05 7:22 PM, Paul DuBois at [EMAIL PROTECTED] wrote: At 19:09 -0700 5/4/05, Scott Haneda wrote: I have a unique key on username and email address in mysql. In my application logic, I want to test for that response on failed insert. Error number 1062 tells me there is a duplicate key, but no way to tell what key that is. The error string returned is more telling, but seems less than ideal to use for stability and upgrade issues. It tells me username is key 2 and email is key 3. So, if error_string ends with 'key 2' etc etc I took care to lowercase the error string, so that's covered. What is not, is that it may not always 'ends with', so I could move to 'contains'. Anyway, I think you see what my concern is, wonder what the preferred method to fix is, or if someone can tell me when, if ever, the error string has ever changed format in mysql. Error strings do change format sometimes. And they won't necessarily be in English. :-) I had a feeling, what is the general method most use to deal with the above scenario? I really don't want to test error strings every time I update mysql, I suppose the safe road is to hand select ahead of time? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Beta 5.0.4: Table 'mysql.host' Does Not Exist
When I installed MySQL-server version 5.0.4 on my Fedora Core 3 system (Linux x86, using RPMs from MySQL.com) with SELinux running in enforcing mode, the server failed to start, possibly due to denials from the SELinux implementation. 'restorecon -R -v /var/lib/mysql' failed to let the server start. 'restorecon -R -v /usr/lib/mysql' failed to let the server start. 'restorecon -v /usr/sbin/mysqld' failed to let the server start. I then disabled SELinux just for the mysqld application. However MySQL 5's server won't start. I located the error log and it has this message: [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist How can I fix this problem? Should I uninstall MySQL (with 'rpm -e') and reinstall it? This should work if I disabled SELinux for mysqld? Thanks for your help Bob Cochran Greenbelt, Maryland, USA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ERROR 1251: Client does not support authentication protocol requested by server; consider upgrading MySQL client
Hassan Schroeder wrote: Mark Sargent wrote: ok, I don't fully understand why, but, mysql is still installed in /usr/bin. What I've done, just for now, is renamed mysql in that dir to mysqlold. I've added /usr/local/mysql/bin to my path. I then tried the cmd mysql and also ./mysql from within the dir /usr/local/mysql/bin, and after entering the correct password, I get the following, [EMAIL PROTECTED] bin]# ./mysql -p Enter password: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) If I run ./usr/bin/mysqlold I can connect. What am I not understanding with this..? If it were me, I'd remove all traces of mysql* anything from the system and start fresh. :-) But in any case, which mysqld process is now running? I'm guessing it's the old one. If so, kill it, and start up your new version using an unambiguous path (e.g., /usr/local/mysql/bin/mysqld_safe). Then try to connect with your new client. HTH, Hi All, yeah, I'm seriously considering removing all trace of mysql. The startup script points to /usr/local/mysql/bin/mysqld_safe. That was why I was having the original problem. Running the latest mysql shouldn't be experiencing connection problems. What I don't understand, is, the old mysql can connect, but not the latest. Weird. Cheers. Mark Sargent. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem connecting to 4.1 server
I am have trouble connecting to the MySQL server 4.1 using the API,
though I have no trouble connecting using the same parameters from the
command line mysql.exe.
I'm using server version 4.1.10 on Windows Server 2003, which was
started with --old-password (to ensure back compatibility with legacy
PHP 4 applications). As a result my mysql server uses the older style,
16-bit password hashing.
I'm using mysql_real_connect, and linking with the libmysql.lib that
ships with ver 4.1.10.
When I connect using mysql_real_connect() to a database with a password,
it fails, and returns a bad handshake error.
However, when I connect using mysql_real_connect() to a database with no
password, it connects without a problem.
This makes me suspect that the client is incorrectly trying to hash with
the newer style 41-bit hashing, against a server using the 16-bit
hashing. Is there any way to tell the client to use older-style hashing?
One of the client flags seems to relate to this,
ie:
#define CLIENT_LONG_PASSWORD 1 /* New more secure passwords */
(mysql_com.h, line 107)
but this should mean that a value of 0 uses short passwords, and I'm
calling mysql_real_connect with a value of 0 for the client flag
argument (though I've also tried with 1 to see if that works).
I have no trouble connecting to the database using the same connection
parameters using the command line mysql.exe.
Do anyone have any idea what I should do?
*** code extract ***
MYSQL my;
mysql_init(my);
if (!mysql_real_connect(my, host, user, password, db, port, NULL, 0))
{
cerr ...
exit(EXIT_FAILURE);
}
else
{
cerr ...
exit(EXIT_SUCCESS);
}
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
