Re: MySQL upgrading
Was not the command mysqldump -u user -p your_database dump.sql easier to do ? Robert L Cochran wrote: I myself want to go from version 4.1.7 to 4.1.9, so I read the upgrade page, which advises you to back up your databases. You do that with mysqldump, and that is briefly explained here: http://dev.mysql.com/doc/mysql/en/upgrading-to-arch.html I added this user comment to the page: --- Before going further, I'm not a deep unix expert, so take my comments with caution. For mysqldump to work, DUMPDIR must be writable by the mysql user. A 'select into outfile' query is executed to get the result set for the *.txt file containing dumped table data, and that file has to be written by the mysql user. For example, on unix systems if you specify /home/well/mysqlbkup as DUMPDIR, then /home/well must be readable by the mysql user and /home/well/mysqlbkup must be writable by the mysql user. When mysqldump creates the *.txt file containing the actual table data, that file is world readable on a unix system. -- It took a long time for me to figure out how to make /home/well/mysqlbkup writable by the mysql user, and I probably did it wrong. If user well is the owner of /home/well, then I changed the permissions from: drwx-- 48 well well /home/well to drwxr-x--- 48 well mysql /home/well and then /home/well/mysqlbkup to: drwxrwxr-x 2 mysql mysql 4096 mysqlbkup On the administrative side I added the 'mysql' user group to the 'well' user group and the 'well' user group to the 'mysql' user group. I probably made mistakes here, but I did succeed in getting my databases backed up. One last note: mysqldump apparently looks at your .my.cnf and executes with the username specified in that file. If that user does not have both select_priv and file_priv access to the database, you won't be able to create a backup of it. I ended up using mysqldump as the root user. Bob Cochran Greenbelt, Maryland, USA Coz Web wrote: Helena, have you tried this link: http://dev.mysql.com/doc/mysql/en/upgrade.html It starts: 2.10. Upgrading MySQL As a general rule, we recommend that when upgrading from one release series to another, you should go to the next series rather than skipping a series. For example, if you currently are running MySQL 3.23 and wish to upgrade to a newer series, upgrade to MySQL 4.0 rather than to 4.1 or 5.0. The following items form a checklist of things you should do whenever you perform an upgrade: ... 2.10.2. Upgrading from Version 4.0 to 4.1 2.10.3. Upgrading from Version 3.23 to 4.0 HTH Coz On Fri, 4 Feb 2005 21:09:21 +0100 (CET), Helena Carlsson [EMAIL PROTECTED] wrote: Hi all, I know this is a silly question but, frankly, I didn't find any useful and straightforward document in dev.mysql.com. I want to upgrade mysql server on a fedora core 3 linux system from 3.23 to the latest version 4.1. First I want to know if is possible, because I have read somewhere that upgrading from 3.23 should be done first to 4.0 and then from 4.0 to 4.1, but I don't know it is right or not ! If it is possible, is there any link to guide ? Thanks, Helena -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Philippe Poelvoorde COS Trading Ltd. +44.(0)20.7376.2401 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query problem
Hi, all I hope somebody can help me. Situation Three tables 1. ++++ | id | name | region | ++++ | 13 | Name1 | 1 | | 15 | Name2 | 2 | | 47 | Name3 | 1 | | 57 | Name4 | 2 | | 65 | Name5 | 2 | | 77 | Name6 | 1 | | 85 | Name7 | 1 | ++++ 2. ++++--+ | id | Date | amount1| current | ++++--+ | 13 | 21.7.1967 | 9900 |N | | 13 | 21.7.1968 | 9800 |J | | 57 | 11.9.1999 | 12800 |J | | 65 | 24.6.1991 | 1200 |N | | 65 | 21.7.1967 | 1275 |J | | 85 | 14.2.2001 | 45000 |J | ++++--+ 3. ++---++ | id | Year | amount2| ++---++ | 13 | 1967 | 100 | | 13 | 1968 | 100 | | 13 | 1969 | 125 | | 15 | 1967 | 200 | | 15 | 1968 | 220 | | 15 | 1969 | 220 | | 47 | 1967 | 500 | | 47 | 1968 | 580 | | 47 | 1969 | 550 | ++---++ In table 1 there are all IDs. In tables 2 and 3 there can be some IDs. As you see, in region 1 there are the IDs 13,47,77 and 85. In table 2 only 13 and 85, in table 3 only 13 and 47. What I would like to have is a result like this: ++---+--+ | region | sum(amount1) if current=J | sum(amount2) | ++---+--+ | 1| 54800 | 1630 | | 2| 14075 | 640 | ++---+--+ Many thanks in advance Joachim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Syntax for Compound IF Statements?
Hi,
I guess your CASE statement should look something like:
CASE WHEN Location=1 THEN 'Downstairs Cat Room'
WHEN Location=2 THEN 'Kitten Room'
WHEN Location=3 THEN 'Quarantine'
ELSE 'Unknown' END AS Location
/Johan
Sue Cram wrote:
Thanks to the people who helped me with my IF statement question last night.
Now I need to carry it one step further to a compound 'IF' statement. Again,
can't find much information in the manuals --
Several people sent me IF (Adopted=1, 'Y', 'N') AS Adopted FROM Animal and it
works great. Now I want my report to show the English Language meaning for a field
('Location' in a table called 'Animal') that contains numeric values. In other
languages it would be something like this (a compound if statement):
IF Location=1
MOVE 'Downstairs Cat Room' to Location
ELSE IF Location=2
MOVE 'Kitten Room' to Location
ELSE IF Location=3
MOVE 'Quarantine ' to Location
ELSE MOVE 'Unknown' to Location
END IF
In other words, I want to test for the comparison being true, and if it isn't true I want to test it again for another value... I couldn't find any information about this construct in the manuals or past list messages. I also tried using the 'CASE .. WHEN .. THEN .. END' but couldn't figure out the syntax errors I was getting.
Thanks again for your help and support-
Sue
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB crash and runaway rollback - help pls
Devananda, the ALTER TABLE creates a temporary table #sql... The rollback is running in that table. The manual contains instructions on how to rename and drop such table. You should upgrade to 4.1.9. That version commits ALTER TABLE at every 10 000 rows, and a runaway rollback can no longer happen. Also, in that version it is easier to drop #sql... tables, because you can simply write: DROP TABLE `#sql...`; Also note that for InnoDB it is fastest to create the table with all the index definitions first, and only after that import the rows. Creating indexes AFTER you have imported the rows is much slower. Your buffer pool is extremely small, only 8 MB. Below you are confusing it to the log buffer. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL technical support from https://order.mysql.com/ - Original Message - From: Devananda [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, February 07, 2005 8:23 PM Subject: InnoDB crash and runaway rollback - help pls Hi, I'm looking for two things - help understanding why InnoDB crashed on our= server this morning, and help speeding up recovery from that crash. We a= re running 4.0.21 binaries on Fedora Core 2 (Tettnang) Linux, 2.6.5-1.358= smp. I'll explain what happened We spent the weekend importing all our data into InnoDB. The last step wa= s to create an index on a table with 28mil rows (ALTER TABLE master_dna_l= ist ADD PRIMARY KEY (email(55)). Since this table isn't critical to other= areas of our website, we turned the site back on while the alter table w= as still running. Things seemed ok for several hours, but this morning, l= ots of threads started locking. Looking in the log showed InnoDB: Warnin= g: difficult to find free blocks from the buffer pool, and vmstat showed= that the CPU and disks were completely idle. SHOW INNODB STATUS would = just hang indefinitely. Seeing no other recourse, I restarted mysqld, whi= ch of course caused a rollback of the alter table. The roll back is progr= essing at about 1% every 12 minutes, which equates to about 20hrs before = the server can be back up - not a good thing. Reading through the mysql d= ocumentation, we found this... http://dev.mysql.com/doc/mysql/en/forcing-recovery.html Starting from MySQL 3.23.53 and 4.0.4, you are allowed to |DROP| = or |CREATE| a table even if forced recovery is used. If you know tha= t a certain table is causing a crash in rollback, you can drop it. Y= ou can use this also to stop a runaway rollback caused by a failing mass imp= ort or |ALTER TABLE|. You can kill the *mysqld* process and set |innodb_force_recovery| to |3| to bring your database up without the rollback. Then |DROP| the table that is causing the runaway rollback. So we tried it - stopped the server (kill -9 at this point since a proper= shutdown just hangs), edit my.cnf and added innodb_force_recovery=3D3 = and skip-networking, start up, drop table master_dna_list, show tabl= es and it's not there - good, stop the server again. waiting the my= sqld process would not terminate, after waiting 10 minutes with the syste= m totally idle, kill -9 again. start mysqld normally, and the rollback is= STILL going. Is there something else I must do to abort this rollback? A= ny help would be appreciated :) My other question is just why did InnoDB crash to begin with? At 3:40am t= his message began to show up in the log file. 050207 3:40:04 InnoDB: WARNING: over 4 / 5 of the buffer pool is occup= ied by=20 InnoDB: lock heaps or the adaptive hash index! Check that your=20 InnoDB: transactions do not set too many row locks.=20 InnoDB: Your buffer pool size is 8 MB. Maybe you should make=20 InnoDB: the buffer pool bigger?=20 InnoDB: Starting the InnoDB Monitor to print diagnostics, including=20 InnoDB: lock heap and hash index sizes. In the documentation, there is this # The size of the buffer InnoDB uses for buffering log data. As soon as # it is full, InnoDB will have to flush it to disk. As it is flushed # once per second anyway, it does not make sense to have it very large # (even with long transactions). innodb_log_buffer_size =3D 8M It explicitly states that it does not make sense to have it very large,= so I had left it at 8M on our server. Later in the morning, this message= begins appearing in the log file... this is about the time when I woke u= p and saw that the server was completely idle - no disk i/o and no cpu ac= tivity. Could anyone please try to explain what happened? Thanks very muc= h for any help or advice. 050207 7:42:33 InnoDB: Warning: difficult to find free blocks from=20 InnoDB: the buffer pool (1342 search iterations)! Consider=20 InnoDB: increasing the buffer pool size.=20 InnoDB: It is also possible that in your Unix
Recursive category table problem (whatever you define it). Help!
Dear friend, I am classifying some items. The items will be stored in a large MySQL database (probably InnoDB engine). At storage time, we would like to append a series of descriptive categories to the item, in order to facilitate friendly, very flexible searches later. For instance item A is in main category food, subcategory organic, sub-subcategory vegetarian. Item B is category organic, sub-category food, sub-sub category baby. As you see the same category can be main category for one type of item, subcategory for another item, sub-subcategory for another. I have been working on this thing for a couple of days now, but I have not been able to come out with any clever solution. I need - good efficiency on multicategory searches - to store data in a relational table (if possible) - to update easily the list of categories (the table) - to address the problem of assigning a different status (main, sub, sub-sub and so on) depending on the type of item to each category I put in the list. Help! -- symbulos partners -.- symbulos - ethical services for your organisation http://www.symbulos.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ERROR 1189 (08S01): Net error reading from master
Hi, I have some problems here with two mysql servers, version 4.1.8; on the master server, binary logs are activated, free disk space is not null; [EMAIL PROTECTED]:~# ls -l /repl/mysql/data/master.* -rw-rw 1 mysql mysql 1814256 2005-02-08 11:43 /repl/mysql/data/master.01 -rw-rw 1 mysql mysql 16 2005-02-07 22:28 /repl/mysql/data/master.index [EMAIL PROTECTED]:~# df -h /repl/mysql/data/ Sys. de fich. Tail. Occ. Free. %Occ. Monté sur /dev/hd0/repl04,0G 607M 3,4G 15% /repl0 the correct grant has been typed on the master server to allow the slave to connect and replicate; (launched with --skip-name-resolve) mysql grant replication slave, file on *.* to [EMAIL PROTECTED] identified by 'pass'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql flush privileges; Query OK, 0 rows affected (0.00 sec) now I change the master to, and ask for the master (in production), to give his data to the slave; mysql change master to MASTER_HOST='db0', MASTER_USER='repl', MASTER_PASSWORD='pass', MASTER_LOG_FILE='master.01'; Query OK, 0 rows affected (0.03 sec) the only thing I can get is: mysql load data from master; ERROR 1189 (08S01): Net error reading from master Any ideas ? is this a bug in 4.1.8 ? Regards, Olivier Kaloudoff CKR Solutions Open Source Mandelieu, France http://www.ckr-solutions.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Syntax for Compound IF Statements?
Harald Fuchs wrote: That's correct, but it can be written shorter and clearer: CASE Lccation WHEN 1 THEN 'Downstairs Cat Room' WHEN 2 THEN 'Kitten Room' WHEN 3 THEN 'Quarantine' ELSE 'Unknown' END AS Location - surely it would be better to have the location ids and location names in a lookup table, and simply write the query as a join between the animals table and the locations table? Hard-coding the meaning of ids into the code itself is never a good idea. - ian -- +---+ | Ian Sales Database Administrator | | | | If your DBA is busy all the time... | | ...he's not doing his job properly | | eBuyer http://www.ebuyer.com | +---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query problem
On Tuesday 08 February 2005 08:09, Joachim Klöfers wrote: Hi, all I hope somebody can help me. bigsnip / Many thanks in advance Joachim That is a very thorny problem. May I observe that you will find things much easier, if you add unique primary keys to tables 2 3, thus: ALTER TABLE whatever_the_table_is_called ADD pri_key MEDIUMINT AUTO_INCREMENT PRIMARY KEY FIRST; You can then left join the second table to itself and get along on the first stage of solving the wider problem. Assuming the second table is called table2, for instance, you could do something like SELECT t2a.id,SUM(IF(t2a.current='J',t2a.amount1,0)) as 'amount1' FROM table2 AS t2a LEFT JOIN table2 as t2b ON t2a.pri_key!=t2b.pri_key AND t2a.id=t2b.id AND t2a.current=t2b.current GROUP BY a.id; (Note, this only solves the 'sum if current' part of the problem, but this is certainly one of the harder aspects that needs solving, anyway) Even that is probably far more tortuous than it needs to be, but I can't think of anything better, at present. Anyway, as I say, I personally don't see how you will solve the wider problem without some means of distinguishing unique records from one another in tables 2 3. I'd also ask you to consider whether it was even wise to attempt to solve this type of thing with a single query, if it means that anyone trying to modify it at a future date is going to have to gaze at it for several hours before they could begin work on the alterations :). Hope that helps to some extent. Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query problem
Just to confirm, is amount2 for region 1 supposed to be 1630 (id 47) or 1955 (ids 13 47)? Coz snip What I would like to have is a result like this: ++---+--+ | region | sum(amount1) if current=J | sum(amount2) | ++---+--+ | 1| 54800 | 1630 | | 2| 14075 | 640 | ++---+--+ Many thanks in advance Joachim -- CozWeb Solutions Ltd http://www.cozweb.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
query problem in num_row
here a syntax $query=$conn-Execute(select * from itemlocation where id limit 1 - 50); $result=mysql_query($query); $num_result=mysql_num_rows($result); and error message are syntax error in $num_result=mysql_num_rows($result); object unknown can someone please give a direction all I want to do is comparing data in itemlocation with a value and if value in item location is smaller then the value I make then it will appear in a message Aji __ Do you Yahoo!? The all-new My Yahoo! - Get yours free! http://my.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query problem
On Tue, 8 Feb 2005 11:37:20 +, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: On Tuesday 08 February 2005 08:09, Joachim Klöfers wrote: Hi, all I hope somebody can help me. bigsnip / Many thanks in advance Joachim That is a very thorny problem. May I observe that you will find things much easier, if you add unique primary keys to tables 2 3, thus: ALTER TABLE whatever_the_table_is_called ADD pri_key MEDIUMINT AUTO_INCREMENT PRIMARY KEY FIRST; You can then left join the second table to itself and get along on the first stage of solving the wider problem. Assuming the second table is called table2, for instance, you could do something like SELECT t2a.id,SUM(IF(t2a.current='J',t2a.amount1,0)) as 'amount1' FROM table2 AS t2a LEFT JOIN table2 as t2b ON t2a.pri_key!=t2b.pri_key AND t2a.id=t2b.id AND t2a.current=t2b.current GROUP BY a.id; (Note, this only solves the 'sum if current' part of the problem, but this is certainly one of the harder aspects that needs solving, anyway) Even that is probably far more tortuous than it needs to be, but I can't think of anything better, at present. Anyway, as I say, I personally don't see how you will solve the wider problem without some means of distinguishing unique records from one another in tables 2 3. I'd also ask you to consider whether it was even wise to attempt to solve this type of thing with a single query, if it means that anyone trying to modify it at a future date is going to have to gaze at it for several hours before they could begin work on the alterations :). Hope that helps to some extent. Dan Hmm.. What's wrong (for the first part) in simply doing : SELECT region, SUM(amount1) FROM t1 , t2 WHERE t1.id=t2.id AND current='J' GROUP BY region works for me. Now to see about the sum of amount2 -- CozWeb Solutions Ltd http://www.cozweb.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Syntax for Compound IF Statements?
Sue Cram [EMAIL PROTECTED] wrote on 02/08/2005 01:18:48 AM:
Thanks to the people who helped me with my IF statement question
last night. Now I need to carry it one step further to a compound
'IF' statement. Again, can't find much information in the manuals --
Several people sent me IF (Adopted=1, 'Y', 'N') AS Adopted FROM
Animal and it works great. Now I want my report to show the
English Language meaning for a field ('Location' in a table called
'Animal') that contains numeric values. In other languages it
would be something like this (a compound if statement):
IF Location=1
MOVE 'Downstairs Cat Room' to Location
ELSE IF Location=2
MOVE 'Kitten Room' to Location
ELSE IF Location=3
MOVE 'Quarantine ' to Location
ELSE MOVE 'Unknown' to Location
END IF
In other words, I want to test for the comparison being true, and if
it isn't true I want to test it again for another value... I
couldn't find any information about this construct in the manuals or
past list messages. I also tried using the 'CASE .. WHEN .. THEN ..
END' but couldn't figure out the syntax errors I was getting.
Thanks again for your help and support-
Sue
SQL can resolve that for you without an IF() as long as you have at least
partially normalized your data. Here's one way to solve this issue. Create
a new table, call it Location.
CREATE TABLE Location (
ID int not null auto_increment primary key
, Locale varchar(25) not null
, UNIQUE(Locale)
);
Then populate the Location table so that you build a look-up list of
ID-Locale pairs (the ID values will be auto-generated)
INSERT Locations (Locale) VALUES ('Downstairs Cat Room'),('Kitten
Room'),('Quarantine');
Now do an OUTER JOIN to optionally match the rows of one table to the rows
of another (we need all of the rows from Animal and only those rows from
Location that match up). I strongly prefer using LEFT JOIN over RIGHT
JOIN. The JOIN will line up the rows in each table so that when the ON
condition(s) is/are true you will see data from one or both tables. In an
OUTER JOIN, those rows that don't satisfy the ON condition from the table
in the _opposite_ direction of the JOIN will get all null values in all of
their columns (even for columns that normally would not be null). By
checking for the NULL value we detect an Animal.Location value that isn't
listed in the Location table.
SELECT Name
, IF(Adopted=1,'Y','N') as Adopted
, IF(Location.Locale Is Null,'Unknown',Location.Locale) as
Location
FROM Animal
LEFT JOIN Location
ON Location.ID = Animal.Location;
This does two things for you:
1) It creates a master list of locations in the data and keeps it out of
your code.
2) It makes your query more flexible. If for some reason you wanted to
change the name of the 'Kitten Room' to 'Animal Nursery' then all you need
to change is that one row of data on the Location table. Since you should
write your front end to work off of the same list (the Location table, not
something hard coded) everything will automatically stay in sync (no code
changes). It makes everything a little easier to manage, trust me.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Strage behaviour with Innodb on MySQL-4.1.9
I use MySQL for my application with InnoDB as primary table.
I found a very strage behaviour of InnoDB table after i alter table
TABLE drop a field at there since this field is not used anymore.
WHen i executre show processlist i found
| 2221 | root | 10.1.20.10:43071| DB | Query | 1045 |
Waiting for tables | insert into TABLE (in_sms_message_id) values
('51104e23G') |
That query was generated using PHP script hit by web client.
I think that my tables was locked, but when i try to insert manually
insert into DB.TABLE(in_sms_message_id) values ('a');
Query OK, 1 row affected (0.01 sec)
So, process id 2221 is waiting for what ?/
This is my innodb status
---TRANSACTION 0 3523706, not started, process no 5646, OS thread id
2858939200
MySQL thread id 2221, query id 6332388 10.1.20.10 root Waiting for tables
insert into DB.TABLE (in_sms_message_id) values ('51104e23G');
So, how to push this query to run ?
Thx
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How do I create a database 179503
Hi I posted a message yesterday 179503, with my problems in getting to the stage of creating a database, I have had an Email with approx 50 .ezm attachments, eventually I found the document on the MYSQL lists to tell me how to set up my "file types" to access these Emails, but without going through each and everyone of these to see what they relate to, I will not have the time to establish "what next". Is there anyway that it is possible to find out the answers to my problems without paying for a support contract! The problems are probably trivial to experienced users, nut I cannot seem to even get to the stage where I am creating a sample database to play around with. Paul Kefford No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.8.6 - Release Date: 07/02/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How do I create a database 179503
[snip] I posted a message yesterday 179503, with my problems in getting to the stage of creating a database, I have had an Email with approx 50 .ezm attachments, eventually I found the document on the MYSQL lists to tell me how to set up my file types to access these Emails, but without going through each and everyone of these to see what they relate to, I will not have the time to establish what next. Is there anyway that it is possible to find out the answers to my problems without paying for a support contract! The problems are probably trivial to experienced users, nut I cannot seem to even get to the stage where I am creating a sample database to play around with. [/snip] RTFM http://dev.mysql.com/doc/mysql/en/create-database.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ERROR 1189 (08S01): Net error reading from master
Hello. mysql grant replication slave, file on . to [EMAIL PROTECTED] identified by 'pass'; Query OK, 0 rows affected, 1 warning (0.00 sec) You should additionally grant SUPER, RELOAD, and SELECT privilege for all tables that you want to load. See: http://dev.mysql.com/doc/mysql/en/replication-howto.html Olivier Kaloudoff [EMAIL PROTECTED] wrote: [-- text/plain, encoding quoted-printable, charset: ISO-8859-1, 61 lines --] Hi, I have some problems here with two mysql servers, version 4.1.8; on the master server, binary logs are activated, free disk space is not null; [EMAIL PROTECTED]:~# ls -l /repl/mysql/data/master.* -rw-rw 1 mysql mysql 1814256 2005-02-08 11:43 /repl/mysql/data/master.01 -rw-rw 1 mysql mysql 16 2005-02-07 22:28 /repl/mysql/data/master.index [EMAIL PROTECTED]:~# df -h /repl/mysql/data/ Sys. de fich. Tail. Occ. Free. %Occ. Mont? sur /dev/hd0/repl04,0G 607M 3,4G 15% /repl0 the correct grant has been typed on the master server to allow the slave to connect and replicate; (launched with --skip-name-resolve) mysql grant replication slave, file on *.* to [EMAIL PROTECTED] identified by 'pass'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql flush privileges; Query OK, 0 rows affected (0.00 sec) now I change the master to, and ask for the master (in production), to give his data to the slave; mysql change master to MASTER_HOST='db0', MASTER_USER='repl', MASTER_PASSWORD='pass', MASTER_LOG_FILE='master.01'; Query OK, 0 rows affected (0.03 sec) the only thing I can get is: mysql load data from master; ERROR 1189 (08S01): Net error reading from master Any ideas ? is this a bug in 4.1.8 ? Regards, Olivier Kaloudoff CKR Solutions Open Source Mandelieu, France http://www.ckr-solutions.com [-- text/plain, encoding 7bit, charset: us-ascii, 4 lines --] -- 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: Slow query log?
Hello. You have an application which executes prepared statements. See: http://dev.mysql.com/doc/mysql/en/c-api-prepared-statements.html MySQL doesn't log to the slow log a prepared statement. You can enable general query log which logs prepared statements. Andrea Gangini [EMAIL PROTECTED] wrote: I've added the following line to my.cnf in order to activate the logging of slow queries: [mysqld] log-long-format log_slow_queries=/var/log/slow-queries.log long_query_time=20 The mysql server version is 4.1.9, but all I see in slow-queries.log the following: # [EMAIL PROTECTED]: mmareuser[mmareuser] @ localhost.localdomain [127.0.0.1] # Query_time: 61 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 # administrator command: Prepare Execute; # Time: 050207 16:29:15 # [EMAIL PROTECTED]: mmareuser[mmareuser] @ localhost.localdomain [127.0.0.1] # Query_time: 53 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 # administrator command: Prepare Execute; # Time: 050207 16:29:17 # [EMAIL PROTECTED]: mmareuser[mmareuser] @ localhost.localdomain [127.0.0.1] # Query_time: 67 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 # administrator command: Prepare Execute; # Time: 050207 16:32:32 # [EMAIL PROTECTED]: mmareuser[mmareuser] @ localhost.localdomain [127.0.0.1] # Query_time: 28 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 # administrator command: Prepare Execute; I thought that the slow query log would dump the SQL of the queries causing slow-downs. Did I made some mistake in configuring my server? -- 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: mysql_init() issue on HPUX
Hello. Operating system error number 13 in a file I check the permission on the files and directories. The user 'mysql' and group 'mysql' have access to the data directory and to the files. Check that you really run the mysqld under the 'mysql' account. Have you applied the patches as mentioned at: http://dev.mysql.com/doc/mysql/en/hp-ux-11-x.html If you don't want to use InnoDB tables, launch the MySQL server with --skip-innodb command line option. [snip] I'm new to the mySql product. I have installed the mySql binaries on my HPUX machine and followed the instructions. I tested that the command line 'mysql' is working and I can create databases, users and import data. I wrote a simple C program that does mysql_init(NULL) and then connects to the database. However, when the program executes the mysql_init function, I get the following error: InnoDB: Unable to lock /opt/mysql-standard-4.1.9-hp-hpux11.00-hppa2.0w/data/ibdata1, error: 13 InnoDB: Check that you do not already have another mysqld process InnoDB: using the same InnoDB data or log files. InnoDB: Error in opening /opt/mysql-standard-4.1.9-hp-hpux11.00-hppa2.0w/data/ibdata1 050207 17:22:12 InnoDB: Operating system error number 13 in a file operation. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: Could not open or create data files. InnoDB: If you tried to add new data files, and it failed here, InnoDB: you should now edit innodb_data_file_path in my.cnf back InnoDB: to what it was, and remove the new ibdata files InnoDB created InnoDB: in this failed attempt. InnoDB only wrote those files full of InnoDB: zeros, but did not yet use them in any way. But be careful: do not InnoDB: remove old data files which contain your precious data! 050207 17:22:12 [ERROR] Can't init databases I check the permission on the files and directories. The user 'mysql' and group 'mysql' have access to the data directory and to the files. Can someone direct my to a possible solution? [EMAIL PROTECTED] wrote: [snip] -- 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]
Difference between 'LIKE' and '='
Hi All, From what I understand the LIKE keyword allows you to do things like include wildcards and match against regular expressions. But I have a question: is there any difference between the following? SELECT lname, fname FROM contacts WHERE lname = 'smith'; SELECT lname, fname FROM contacts WHERE lname LIKE 'smith'; I'm having difficulty figuring out the difference between these two queries, if there is any. Can someone clarify? Sincerely, -Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query problem
On Tue, 08 Feb 2005 15:52:02 +0100, Joachim Klöfers [EMAIL PROTECTED] wrote: Oh, oh, Joachim, not able to calculate, but asking questions about queries. Coz , of course its supposed to be 1955 (id 13 47) Joachim Coz Web schrieb: Just to confirm, is amount2 for region 1 supposed to be 1630 (id 47) or 1955 (ids 13 47)? Thought I'd check I hadn't missed something. Do the results *have* to be from a single query? I would suggest using something like the previous query for amount1 and this for amount2 and then combining them in your application. SELECT region, SUM(amount2) FROM t1 LEFT JOIN t3 USING(id) GROUP BY region This will (as I believe Daniel suggested) keep things relatively simple, avoiding an overly complex query that you cannot maintain in the future. That is, unless someone on this list provides a simple single-query solution, which they very well may do. HTH Coz -- CozWeb Solutions Ltd http://www.cozweb.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: [PHP-DB] mysqli connection problem
Hello, This question was asked many times, but I can't find a good answer. I am getting this error message while trying to connect to MySQL server (PHP + PEAR::DB_DataObject): Can't connect to local MySQL server through socket '' (111) (notice '' - is that right?) Does anyone have any ideas about how to get rid of this error? Configuration: * RedHat Enterprise Linux AS3 * Apache 2.0.46 * PHP 5.0.2 * MySQL 4.1.9, mysqli ext. Thanks! [Note: reply-to set to php-db as it's multimailed and I don't see this as a mysql general issue, nor php general, but feh.] Well, I don't know the possible reasons for this, so I mailed it to all suitable lists. Looks to be set blank/null/etc. You may be slightly tricked by the existence of both of these - from your standard php.ini... - ; Default socket name for local MySQL connects. If empty, uses the built- in mysql.default_socket = ; Default socket name for local MySQL connects. If empty, uses the built- in ; MySQL defaults. mysqli.default_socket = Didn't help me. MySQL socket does exist - /var/lib/mysql/mysql.sock (default location). mysqli.default_socket is NOT set to null but to this value (manually, as you advised) Again, 1. Connection to localhost still fails with message Can't connect to local MySQL server through socket '' (111) 2. Connection to server's domain name fails too (!) with message Access denied for user 'user'@'example.com' (using password: YES) 3. Connection to localhost through mysql shows the following config mysql status -- mysql Ver 14.7 Distrib 4.1.9, for pc-linux-gnu (i686) Connection id: 42 Current database: Current user: [EMAIL PROTECTED] SSL:Not in use Current pager: stdout Using outfile: '' Using delimiter:; Server version: 4.1.9-standard Protocol version: 10 Connection: Localhost via UNIX socket Server characterset:latin1 Db characterset:latin1 Client characterset:latin1 Conn. characterset:latin1 UNIX socket:/var/lib/mysql/mysql.sock Uptime: 1 day 3 hours 1 min 7 sec Threads: 3 Questions: 272 Slow queries: 0 Opens: 64 Flush tables: 1 Open tables: 26 Queries per second avg: 0.003 -- Seems to be a security issue... Any more ideas about these symptoms? - I can't see any reason why you wouldn't be able to use the same socket - you're connecting to the same exact server, it's just a path. You should be perfectly able to set your mysqli.default_socket to the same as the mysql.default_socket. I can't tell you what the default for your system is, but there are php functions to get configuration options, if you don't have it in any obvious places. Cheers, -- - Martin Norland, Database / Web Developer, International Outreach x3257 The opinion(s) contained within this email do not necessarily represent those of St. Jude Children's Research Hospital. -- 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: Difference between 'LIKE' and '='
Joshua Beall wrote: Hi All, From what I understand the LIKE keyword allows you to do things like include wildcards and match against regular expressions. But I have a question: is there any difference between the following? SELECT lname, fname FROM contacts WHERE lname = 'smith'; SELECT lname, fname FROM contacts WHERE lname LIKE 'smith'; I'm having difficulty figuring out the difference between these two queries, if there is any. Can someone clarify? - er, there is no difference as you've written it. But if you use wildcards, you get a very different set of results: SELECT lname, fname FROM contacts WHERE lname = 'smith'; ... will bring back all rows where lname is exactly 'smith' SELECT lname, fname FROM contacts WHERE lname LIKE '%smith%'; ... will bring back all rows where lname contains the letters 's m i t h', in that order, such as Smithfield, Aerosmith, Nasmith, Smithsonian... - ian -- +---+ | Ian Sales Database Administrator | | | | If your DBA is busy all the time... | | ...he's not doing his job properly | | eBuyer http://www.ebuyer.com | +---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Difference between 'LIKE' and '='
Hi Joshua, all! Am Di, den 08.02.2005 schrieb Joshua Beall um 17:18: Hi All, From what I understand the LIKE keyword allows you to do things like include wildcards and match against regular expressions. But I have a question: is there any difference between the following? SELECT lname, fname FROM contacts WHERE lname = 'smith'; SELECT lname, fname FROM contacts WHERE lname LIKE 'smith'; I'm having difficulty figuring out the difference between these two queries, if there is any. Can someone clarify? Semantically, they are equivalent and must return the same results. But 'LIKE' may/will cause the server to take a different execution path than '=', one in which regular expressions are accepted. I will not make a specific claim about a specific system / version, let alone predict numbers, but I sure expect that the '=' predicate will be evaluated faster than 'LIKE', so the first statement will yield a better performance. Should it be slower, you would surely have found a bug ;-) In December, we had someone using 'LIKE' for numbers. It worked, but the performance gain from switching to '=' was enormous for him. (My guess is that 'LIKE' forced the values to be converted into strings first ... ) So: If possible, use '=' and spare 'LIKE' for those cases where it is really needed. HTH, Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com Office: (+49 30) 417 01 487 Are you MySQL certified? 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: Difference between 'LIKE' and '='
I should have included the context of this question when I originally asked
it, but I'll include it here now with my apologies:
The reason I am asking is because I have a search method in a PHP class that
searchs a table based on a specific field. By way of example, you would do
something like this:
$result = $Contacts-search('lname','smith');
Now right now, all it does is run the query:
SELECT * FROM contacts WHERE lname = 'smith';
Then stores the result set in an array and returns it.
What I am wondering is, is there any reason why I should now change the
query to ($field and $searchFor get filled in with their respective values):
SELECT * FROM contacts WHERE $field LIKE '$searchFor';
This would allow me to do this:
$Contacts-search('lname','smith%');
And I would get people with lastname Smith and Smithers and Smithereens,
etc.
I'm aware of security concerns like SQL injection and so forth and am
handling them, but I've omitted all that code for the sake of brevity. No
need to warn me about all that.
-jb
Joshua Beall [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
Hi All,
From what I understand the LIKE keyword allows you to do things like
include wildcards and match against regular expressions.
But I have a question: is there any difference between the following?
SELECT lname, fname FROM contacts WHERE lname = 'smith';
SELECT lname, fname FROM contacts WHERE lname LIKE 'smith';
I'm having difficulty figuring out the difference between these two
queries, if there is any. Can someone clarify?
Sincerely,
-Josh
--
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: Difference between 'LIKE' and '='
| Hi All, | | From what I understand the LIKE keyword allows you to do things like include | wildcards and match against regular expressions. | | But I have a question: is there any difference between the following? | | SELECT lname, fname FROM contacts WHERE lname = 'smith'; | SELECT lname, fname FROM contacts WHERE lname LIKE 'smith'; | | I'm having difficulty figuring out the difference between these two queries, | if there is any. Can someone clarify? | | Sincerely, | -Josh As long the expression on the right hand side of the equal sign uses no wildcard characters (% or _), the two WHERE conditions you've written are functionally equivalent; they should get the same rows for their respective result sets. However, there *may* be a performance difference between the two. I don't know if MySQL treats the two differently in terms of choosing an access path but DB2 will normally choose a better access path for '=' than 'LIKE', regardless (I think) of whether the LIKE expression contains wildcards. My gut hunch is that if your LIKE expression is going to contain no wildcards, you should probably write it as an '=' simply because it is likely to perform better. If anyone reading this is knowledgeable on MySQL performance, please jump in and correct me if I'm wrong. Rhino --- rhino1 AT sympatico DOT ca There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies. - C.A.R. Hoare -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Install MySql 4.0.23???
I already have a version of MySql 4.1.17 running on a different server, but am trying to setup v.4.0.23 to see if it will allow me to use PHPBB's software. I'm finding that 4.0.23 isn't as easy to set up as the later versions. I modified the my.ini file and put it in the \windows directory for Windows Server 2003. When I try to use the MySql Administator, and put in the server name, root account and password defined in the my.ini file, it doesn't connect, using 'localhost'. What am I doing incorrectly? Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB crash and runaway rollback - help pls
On Tue, 8 Feb 2005, Heikki Tuuri wrote: You should upgrade to 4.1.9. That version commits ALTER TABLE at every 10 000 rows, and a runaway rollback can no longer happen. This is very nice! Are there any plans for the same with INSERT ... SELECT -type statements? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
selection problem
Dear All, I have the following problem: The table in a database is organized like this one: Sample species_species_b species_c A1 0 5 0 A2 0 0 3 A3 1 1 5 I would like to create a selection, which contains only samples, containing a number of a species_a_ species_b or species_c. It should be like this: Sample A1: 5 species_b Sample A2: 3 species_c Sample A3: 1 species_a, 1 species_b and 1 species_c With the select command I can't do it. Thanks for suggestions. Jan Virus checked by G DATA AntiVirusKit -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Recursive category table problem (whatever you define it). Help!
There are lots of ways to do this, from simple (lookup table and bridge table) to more complex (eg dynamic trees of nodes and edges). You'll likely have to experiment to find the solution that performs best in your context. Often simple is best. Here's a simple representation I used for a movie studio's categorised collection of its film reels. Supposing your items are in a table named 'items', create a lookup table for all your 'categories', for example (cat_id int primary key, cat_desc char(20) ), and a table called 'item_categories', child of items and of categories, for example (item_list_id int primary key, item_id referencing items.item_id, cat_id referencing categories.cat_id, order_num smallint ). Then for each category which is applied to an item, add a row to item_categories setting cat_id to point at the chosen category, setting item_id to point at the item, and setting order_num to the desired value where order_num=1 means 'main' category, 2=sub, 3=sub-sub etc. The frontend app then has only to present a user interface for the item_categories table ensuring that the order_nums are sequential from 1 and without dupes for any given cat_id. PB - symbulos partners wrote: Dear friend, I am classifying some items. The items will be stored in a large MySQL database (probably InnoDB engine). At storage time, we would like to append a series of descriptive categories to the item, in order to facilitate friendly, very flexible searches later. For instance item A is in main category food, subcategory organic, sub-subcategory vegetarian. Item B is category organic, sub-category food, sub-sub category baby. As you see the same category can be main category for one type of item, subcategory for another item, sub-subcategory for another. I have been working on this thing for a couple of days now, but I have not been able to come out with any clever solution. I need - good efficiency on multicategory searches - to store data in a relational table (if possible) - to update easily the list of categories (the table) - to address the problem of assigning a different status (main, sub, sub-sub and so on) depending on the type of item to each category I put in the list. Help! -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.8.5 - Release Date: 2/3/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql permissions
I have problems with access permissions on a Solaris 9 installtion of MySql-max 4.1. My user table shows this select * from user; | Host| User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | | localhost | racadmin | *01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C | Y | Y | Y | Y | Y | Y | Y | Y | Y| Y | N | Y | Y | Y | Y| Y | Y | Y| Y| Y | Y| || | | 0 | 0 | 0 | | % | racadmin | *01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C | Y | Y | Y | Y | Y | Y | Y | Y | Y| Y | N | Y | Y | Y | Y| Y | Y | Y| Y| Y | Y| || | | 0 | 0 | 0 | But while connecting I get this error Shellid uid=0(root) gid=1(other) groups=1(other),0(root),2(bin),3(sys),4(adm),5(uucp),6(mail),7(tty),8(lp),9(nuucp),12(daemon) shell mysql -D mysql -u racadmin ERROR 1045 (28000): Access denied for user 'racadmin'@'localhost' (using password NO) It's a multi-interface box. ifconfig -a lo0: flags=1000849UP,LOOPBACK,RUNNING,MULTICAST,IPv4 mtu 8232 index 1 inet 127.0.0.1 netmask ff00 eri0: flags=1000843UP,BROADCAST,RUNNING,MULTICAST,IPv4 mtu 1500 index 2 inet 10.3.30.162 netmask broadcast 10.3.255.255 ether 0:3:ba:14:6e:f7 eri1: flags=1000842BROADCAST,RUNNING,MULTICAST,IPv4 mtu 1500 index 3 inet 0.0.0.0 netmask 0 ether 0:3:ba:14:6e:f8 hdc0: flags=1000843UP,BROADCAST,RUNNING,MULTICAST,IPv4 mtu 1500 index 4 inet 172.16.1.2 netmask broadcast 172.16.255.255 ether 0:7:10:99:ec:3f hdc1: flags=1000843UP,BROADCAST,RUNNING,MULTICAST,IPv4 mtu 1500 index 5 inet 192.168.1.2 netmask ff00 broadcast 192.168.1.255 ether 0:7:10:99:ec:40 Does anyone have any clue on what is happening? Thanks, Nupur -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB crash and runaway rollback - help pls
Tobias, - Alkuperäinen viesti - Lähettäjä: Tobias Asplund [EMAIL PROTECTED] Vastaanottaja: Heikki Tuuri [EMAIL PROTECTED] Kopio: mysql@lists.mysql.com Lähetetty: Tuesday, February 08, 2005 7:46 PM Aihe: Re: InnoDB crash and runaway rollback - help pls On Tue, 8 Feb 2005, Heikki Tuuri wrote: You should upgrade to 4.1.9. That version commits ALTER TABLE at every 10 000 rows, and a runaway rollback can no longer happen. This is very nice! Are there any plans for the same with INSERT ... SELECT -type statements? the problem in INSERT ... SELECT ... is that if we commit that kind of statement at every 10 000 rows, and mysqld crashes, then we cannot roll back the entire SQL statement, and the binlog will not be consistent with the actual contents of the database. Regards, Heikki -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: max key file length
Hello,
Am Montag, den 07.02.2005, 17:16 + schrieb sirisha gnvg:
...
Thank you for your reply.You suggested me that both
key_reflength and max_key_file_length can be read from key
file.We just need to find the offset at which they are stored
in key file.But I have searched all files containing key
as part of their name in MySQL 4.1.9 folder for
max_key_file_length and all those files all c or c++ program
files.
Then what do you mean by finding offset?Please once again
explain in detail what key file is and what it contains.
A MySQL MyISAM table consists of the files
$DATADIR/databas_name/table_name.frm,
$DATADIR/databas_name/table_name.MYD and
$DATADIR/databas_name/table_name.MYI.
The .MYI file is the file, which contains the indexes. The indexes are
often also called 'keys'. thus, the indexes file is the key file.
Look into myisam/mi_open.c. You will find kfile=my_open(...). This is
the place, where the key file is opened. Some lines later
my_read(kfile,(char*) share-state.header...) the header structure is
read. See myisam/myisamdef.h, typedef struct st_mi_state_info. This
includes struct { ... } header. Back to mi_open.c. Some lines later,
my_seek(kfile,0L,MY_SEEK_SET the key file is rewound. The next read
starts from the beginning. Now, my_read(kfile,disk_cache,info_length.
The full fixed part of the key file, including the header, is read.
Important values are picked out of the buffer with
mi_state_info_read(disk_cache, share-state). Some lines later,
disk_pos=my_n_base_info_read(disk_cache+base_pos, share-base), other
important values are extracted. Both functions are also defined in
mi_open.c.
If you can re-use these functions, you are done. Otherwise, you can add
the sizes, which are picked off the buffer in the above functions. So
you get the offset of each value in the file.
If you have a source tree, you will find a file Docs/internals.texi. It
describes, how you can access the 'real' internals.texi. In this file
you find an explanation, how the key file is structured. This may help
you to understand, what you see in the functions mentioned above.
Regards,
Ingo
--
Ingo Strüwing, Senior Software Developer
MySQL AB, www.mysql.com
Office: +49 30 43672407
Are you MySQL certified? www.mysql.com/certification
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Time Zones and Daylight Saving
I'm storing datetime values in UTC for an international site. Then retrieving the data using 'select convert_tz(created, 'GMT','MST') from table_name', so that the datatime appears in the correct local datetime for the user. Does this method take into account daylight savings? If not, how can this be achieved? or perhaps I'm barking up the completely wrong tree! Thanks, Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Selecting first non-null values from a set of rows
Hello, Say I get these rows in a regular query: col1col2col3 --- NULLB1 NULL NULLNULLNULL A3 B3 NULL A4 NULLC4 A5 B5 C5 NULLB6 C6 (It's important to keep the rows in that order). I want to get 1 row of the first non-null values from every column.. the row: A3 B1 C4. Like the COALESCE function in MySQL, but on rows. Please help... -thanks, Eli -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [PHP-DB] mysqli connection problem
My intention of setting the reply-to was not to offend, or claim you were wrong - merely to move the discussion to the list I felt (and still feel - as I've done it again) is most appropriate for the question. anyway, back to the task at hand ( first: I'm assuming you restarted apache after changing your php.ini - otherwise, that's your problem. ) Whatever the case, the error message clearly shows that it is not reading the value, however it's set - because the error is still showing a blank string for the socket. If it were a permissions problem with the socket, you wouldn't be getting that exact error - at the very least, it would still show the correct socket path. More likely, it would also error with a cannot open socket or equivalent. You may wish to examine ini_get(), ini_get_all(), phpinfo(), or php_ini_scanned_files(), to verify if it is set, or to see if it's being overwritten somewhere. Cheers, Denis Gerasimov wrote: Didn't help me. MySQL socket does exist - /var/lib/mysql/mysql.sock (default location). mysqli.default_socket is NOT set to null but to this value (manually, as you advised) Again, 1. Connection to localhost still fails with message Can't connect to local MySQL server through socket '' (111) 2. Connection to server's domain name fails too (!) with message Access denied for user 'user'@'example.com' (using password: YES) 3. Connection to localhost through mysql shows the following config mysql status -- mysql Ver 14.7 Distrib 4.1.9, for pc-linux-gnu (i686) Connection id: 42 Current database: Current user: [EMAIL PROTECTED] SSL:Not in use Current pager: stdout Using outfile: '' Using delimiter:; Server version: 4.1.9-standard Protocol version: 10 Connection: Localhost via UNIX socket Server characterset:latin1 Db characterset:latin1 Client characterset:latin1 Conn. characterset:latin1 UNIX socket:/var/lib/mysql/mysql.sock Uptime: 1 day 3 hours 1 min 7 sec Threads: 3 Questions: 272 Slow queries: 0 Opens: 64 Flush tables: 1 Open tables: 26 Queries per second avg: 0.003 -- Seems to be a security issue... Any more ideas about these symptoms? - I can't see any reason why you wouldn't be able to use the same socket - you're connecting to the same exact server, it's just a path. You should be perfectly able to set your mysqli.default_socket to the same as the mysql.default_socket. I can't tell you what the default for your system is, but there are php functions to get configuration options, if you don't have it in any obvious places. Cheers, -- - Martin Norland, Database / Web Developer, International Outreach x3257 The opinion(s) contained within this email do not necessarily represent those of St. Jude Children's Research Hospital. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- - Martin Norland, Database / Web Developer, International Outreach x3257 The opinion(s) contained within this email do not necessarily represent those of St. Jude Children's Research Hospital. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AW: selection problem
Jan Bartholdy wrote:
Dear All,
I have the following problem:
The table in a database is organized like this one:
Samplespecies_species_b species_c
A10 5 0
A20 0 3
A31 1 5
I would like to create a selection, which contains only samples,
containing
a number of a species_a_ species_b or species_c. It should be like this:
Sample A1: 5 species_b
Sample A2: 3 species_c
Sample A3: 1 species_a, 1 species_b and 1 species_c
With the select command I can't do it. Thanks for suggestions.
Jan
You can... if you really want to...
If your table is defined as
create table (sample varchar(10), sp_a int, sp_b int, sp_c int);
Values are inserted as
insert into t ('A1',0,5,0),('A2',0,0,3),('A3',1,1,5);
Now a simple select gives you
mysql select * from t;
++--+--+--+
| sample | sp_a | sp_b | sp_c |
++--+--+--+
| A1 |0 |5 |0 |
| A2 |0 |0 |3 |
| A3 |1 |1 |5 |
++--+--+--+
3 rows in set (0.00 sec)
If you want the output as described, try
select
concat(Sample ,sample,: ,
if(sp_a0,concat(sp_a, species_a),),
if(sp_a0(sp_b0||sp_c0),, ,),
if(sp_b0,concat(sp_b, species_b),),
if(sp_b0sp_c0,, ,),
if(sp_c0,concat(sp_c, species_c),)) Species
from t;
And you will get
+--+
| Species |
+--+
| Sample A1: 5 species_b |
| Sample A2: 3 species_c |
| Sample A3: 1 species_a, 1 species_b, 5 species_c |
+--+
3 rows in set (0.00 sec)
That's quiet near, I guess
Frank Busch
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Converting Text columns from mysql 4.0 to 4.1
Hi! We have a problem converting our 4.0 text columns from a Hong Kong database to 4.1. In order to get the conversions to work generally speaking we build our databases with default character set utf8 - it means the German products still work, and the English ones, and the Chinese ones, and the Anyway, we ran into a problem on the Hong Kong platform where the text column imports as a single space to 4.1... If I look at the data in 4.0 I see actual text (I suppose, it's mostly jibberish on my screen), while in 4.1 all I have after the import is a single space character. If I change the column type to blob (from text) I can get the data imported without problem, except that the data is now in a blob column. If I try to alter the table to a text column, I am left with the single spaces again. Looking at the data that does get affected (not all records suffer this fate, just some) it appears that they have multiple languages, for example Chinese or more often Japanese, together with something like an email address which is written in latin type characters. I can post a new entry through the webapp with mixed languages, it's just the export/import that seems to be be letting us down - or converting the blob to a text in 4.1 after the fact. I even tried building a duplicate table format and doing an INSERT SELECT where the source is a blob and the target is a text, and that also fails. Clearly I can't convert the rest of my databases if there is a chance that our message bodies will be munged With about 100 databases each with 60 tables it's not even going to be easy to try and script it in such a way that I could do a dump and an import with something changing the table type in the .sql file from text to blob, let alone the time it will take us to first test the Application and web servers to see if making the change to a blob column will affect us in any way. Do I need to be doing all this work... Is there something I have done incorrectly? Is this a bug that someone is fixing and will go away next version? I can provide the dump files if someone wants to test... Let me know. Best Regards, Bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql permissions
Nupur Jain [EMAIL PROTECTED] wrote on 02/08/2005 01:26:06 PM: I have problems with access permissions on a Solaris 9 installtion of MySql-max 4.1. My user table shows this select * from user; | Host| User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | | localhost | racadmin | *01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C | Y | Y | Y | Y | Y | Y | Y | Y | Y| Y | N | Y | Y | Y | Y| Y | Y | Y| Y| Y | Y | || | | 0 | 0 | 0 | | % | racadmin | *01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C | Y | Y | Y | Y | Y | Y | Y | Y | Y| Y | N | Y | Y | Y | Y| Y | Y | Y| Y| Y | Y | || | | 0 | 0 | 0 | But while connecting I get this error Shellid uid=0(root) gid=1(other) groups=1(other),0(root),2(bin),3(sys), 4(adm),5(uucp),6(mail),7(tty),8(lp),9(nuucp),12(daemon) shell mysql -D mysql -u racadmin ERROR 1045 (28000): Access denied for user 'racadmin'@'localhost' (using password NO) snip Does anyone have any clue on what is happening? Thanks, Nupur You are attempting to login with an ID that has a password on it but you aren't giving the password. add the -p option to your startup line like this: mysql -D mysql -u racadmin -p and you will be prompted to provide the password. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Selecting first non-null values from a set of rows
Eli [EMAIL PROTECTED] wrote on 02/08/2005 02:26:41 PM: Hello, Say I get these rows in a regular query: col1 col2col3 --- NULLB1NULL NULLNULLNULL A3 B3 NULL A4 NULLC4 A5 B5 C5 NULLB6 C6 (It's important to keep the rows in that order). I want to get 1 row of the first non-null values from every column.. the row: A3 B1 C4. Like the COALESCE function in MySQL, but on rows. Please help... -thanks, Eli IF you want all 3 columns to be non-null, make that a condition of your query. SELECT col1, col2, col3 FROM sometable WHERE col1 is not null AND col2 is not null AND col3 is not null LIMIT 1; However, you cannot guarantee a repeatable order to the results of any query unless you force the engine to sort the results by providing an ORDER BY clause to your query. Without an ORDER BY, the query engine is free to respond with records in any order it pleases. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
optimize table and replication failure
I issued a optimize table statement on my master which failed with a lock wait timeout message due to some competing queries running at the same time. Now the statement has been replicated to my slaves, and it completes successfully. However it's killing the slave input thread with the following message in my error log: 050208 15:10:38 [ERROR] Slave: Query caused different errors on master and slave. Error on master: 'Lock wait timeout exceeded; try restarting transaction' (1205), Error on slave: 'no error' (0). Default database: 'MyDB'. Query: 'optimize table My_Table', Error_code: 0 050208 15:10:38 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with SLAVE START. We stopped at log 'db-bin.000340' position 73609938 What's the correct way to work around this? I don't mind if the optimize table statement is skipped on the slaves. I just want replication to continue. MySQL versions 4.1.7 on the master, 4.1.9 on the slave. Thanks. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Install MySql 4.0.23???
I already have a version of MySql 4.1.17 running on a different server, but am trying to setup v.4.0.23 to see if it will allow me to use PHPBB's software. I'm finding that 4.0.23 isn't as easy to set up as the later versions. I modified the my.ini file and put it in the \windows directory for Windows Server 2003. When I try to use the MySql Administator, and put in the server name, root account and password defined in the my.ini file, it doesn't connect, using 'localhost'. What am I doing incorrectly? Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Selecting first non-null values from a set of rows
Eli [EMAIL PROTECTED] wrote on 02/08/2005 02:26:41 PM: Hello, Say I get these rows in a regular query: col1 col2col3 --- NULLB1NULL NULLNULLNULL A3 B3 NULL A4 NULLC4 A5 B5 C5 NULLB6 C6 (It's important to keep the rows in that order). I want to get 1 row of the first non-null values from every column.. the row: A3 B1 C4. Like the COALESCE function in MySQL, but on rows. Please help... -thanks, Eli IF you want all 3 columns to be non-null, make that a condition of your query. SELECT col1, col2, col3 FROM sometable WHERE col1 is not null AND col2 is not null AND col3 is not null LIMIT 1; However, you cannot guarantee a repeatable order to the results of any query unless you force the engine to sort the results by providing an ORDER BY clause to your query. Without an ORDER BY, the query engine is free to respond with records in any order it pleases. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Sorry, but that is not the case I'm looking for.. :-( The row I need is: A3 B1 C4 (the first non-null values from several rows). Your query will return: A5 B5 C5 (row 5 only). Mabye there's a way to use COALESCE function in MySQL, but I couldn't figure how... -thanks, Eli -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ERROR 1189 (08S01): Net error reading from master
Hi, As no one answered to the question below, I'm wondering wether it's a FAQ ... or a bug for which I should fill a report .. My network is ok between the two machines, I can ssh from one to another and any traffic can flow. But this error prevents me to start replication at all.. Olivier On Tue, 8 Feb 2005, Olivier Kaloudoff wrote: Hi, I have some problems here with two mysql servers, version 4.1.8; on the master server, binary logs are activated, free disk space is not null; [EMAIL PROTECTED]:~# ls -l /repl/mysql/data/master.* -rw-rw 1 mysql mysql 1814256 2005-02-08 11:43 /repl/mysql/data/master.01 -rw-rw 1 mysql mysql 16 2005-02-07 22:28 /repl/mysql/data/master.index [EMAIL PROTECTED]:~# df -h /repl/mysql/data/ Sys. de fich. Tail. Occ. Free. %Occ. Monté sur /dev/hd0/repl04,0G 607M 3,4G 15% /repl0 the correct grant has been typed on the master server to allow the slave to connect and replicate; (launched with --skip-name-resolve) mysql grant replication slave, file on *.* to [EMAIL PROTECTED] identified by 'pass'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql flush privileges; Query OK, 0 rows affected (0.00 sec) now I change the master to, and ask for the master (in production), to give his data to the slave; mysql change master to MASTER_HOST='db0', MASTER_USER='repl', MASTER_PASSWORD='pass', MASTER_LOG_FILE='master.01'; Query OK, 0 rows affected (0.03 sec) the only thing I can get is: mysql load data from master; ERROR 1189 (08S01): Net error reading from master Any ideas ? is this a bug in 4.1.8 ? Regards, Olivier Kaloudoff CKR Solutions Open Source Mandelieu, France http://www.ckr-solutions.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Selecting first non-null values from a set of rows
Eli [EMAIL PROTECTED] wrote on 02/08/2005 03:27:42 PM: Eli [EMAIL PROTECTED] wrote on 02/08/2005 02:26:41 PM: Hello, Say I get these rows in a regular query: col1 col2col3 --- NULLB1NULL NULLNULLNULL A3 B3 NULL A4 NULLC4 A5 B5 C5 NULLB6 C6 (It's important to keep the rows in that order). I want to get 1 row of the first non-null values from every column.. the row: A3 B1 C4. Like the COALESCE function in MySQL, but on rows. Please help... -thanks, Eli IF you want all 3 columns to be non-null, make that a condition of your query. SELECT col1, col2, col3 FROM sometable WHERE col1 is not null AND col2 is not null AND col3 is not null LIMIT 1; However, you cannot guarantee a repeatable order to the results of any query unless you force the engine to sort the results by providing an ORDER BY clause to your query. Without an ORDER BY, the query engine is free to respond with records in any order it pleases. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Sorry, but that is not the case I'm looking for.. :-( The row I need is: A3 B1 C4 (the first non-null values from several rows). Your query will return: A5 B5 C5 (row 5 only). Mabye there's a way to use COALESCE function in MySQL, but I couldn't figure how... -thanks, Eli -- With the warning about ordering in mind you could try this: SELECT @col1 = col1 FROM sometable WHERE col1 is not null LIMIT 1; SELECT @col2 = col2 FROM sometable WHERE col2 is not null LIMIT 1; SELECT @col1 as col1, @col2 as col2, col3 FROM sometable WHERE col3 is not null LIMIT 1; Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Simmering FT Queries
I'm evaluating MySQL FT search, and so far it's been
very disappointing. The queries on a test table of
about 2 million rows with Text columns (average 75
words per text column) are extremely slow, compared to
a regular FT search engine, like Lucene.
What's disturbing is that it doesn't consume any
significant CPU resources; it just simmers at around
2% to 5% utilization.
Please note that this is a development machine with a
single connection and no concurrent updates, so
locking is NOT an issue here. I'm just baffled that
MySQL idles by all this time doing virtually nothing
and taking forever to return an FT query that -- with
brute force -- it could've returned faster.
The query doesn't return the text columns themselves,
so there's no hard disk thrashing. It should
theoretically use an inverted FT index to get the list
of documents matching the word and just return the
corresponding primary keys from the table.
The table has the folloing structure:
IDCol int,
One text,
Two text,
three text
I have an FT index on each text columns, and the query
looks like this:
select IDCol from MyTable where match(One) against
('keyword') limit 2000
The query optimizer does the use the FT index built on
column One.
Why does the query simmer for so long with virtually
no CPU utilization?
__
Do you Yahoo!?
Yahoo! Mail - Easier than ever with enhanced search. Learn more.
http://info.mail.yahoo.com/mail_250
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problems with decimal numbers
Hello, When I try to insert decimal numbers like 12857.59, 13858.58 or 14785.60, they are inserted as 12857.58, 13858.57 and 14785.59 ?! DataType is DECIMAL 10,2 Any idea of a way to avoid this annoying issue ? Thanks. Regards. Stéphane. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Selecting first non-null values from a set of rows
Eli ha scritto: Hello, Say I get these rows in a regular query: col1col2col3 --- NULL B1 NULL NULLNULLNULL A3 B3 NULL A4 NULLC4 A5 B5 C5 NULLB6 C6 (It's important to keep the rows in that order). I want to get 1 row of the first non-null values from every column.. the row: A3 B1 C4. Like the COALESCE function in MySQL, but on rows. Please help... -thanks, Eli SELECT * FROM tab WHERE ISNULL(col1) LIMIT 1 UNION SELECT * FROM tab WHERE ISNULL(col2) LIMIT 1 UNION SELECT * FROM tab WHERE ISNULL(col3) LIMIT 1 In your case this will return only 2 rows because union remove duplicates and row 2 satisfy both query 2 and 3 Hope it helps francesco -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Install Question - mysql starts and then stops immediately
Here's hoping you can help. I have installed Redhat Linux Fedora Core 3, server version from ISO's burned on 2/2/05. I have downloaded the latest MySQL 4.1.9-0 rpms, done an rpm -e and upgraded the MySQL-server, MySQL-client, MySQL-devel and MySQL-shared. Basically, I followed the instructions in http://www.whoopis.com/howtos/php5-mysql4-FC3-rpm.html Invoking /etc/init.d/mysql start does not yeild a running mysqld. In /var/lib/mysql/hostname.err, I see the mysqld start followed by mysqld stop a second or so later. invoking /usr/sbin/mysqld --verbose --help returns nothing. Can anyone help me get MySQL upgraded? thanks -- Chuck Herrick mailto:[EMAIL PROTECTED] 512 289 0926 (cell) 830 839 4437 (home) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ERROR 1189 (08S01): Net error reading from master
Just a shot in the dark do you have skip-networking or bind-address uncommented in your my.cnf ? Reading better what you write I suppose the answer is yes ;) can you connect from one server to the other using the replication user ? Olivier Kaloudoff ha scritto: Hi, As no one answered to the question below, I'm wondering wether it's a FAQ ... or a bug for which I should fill a report .. My network is ok between the two machines, I can ssh from one to another and any traffic can flow. But this error prevents me to start replication at all.. Olivier On Tue, 8 Feb 2005, Olivier Kaloudoff wrote: Hi, I have some problems here with two mysql servers, version 4.1.8; on the master server, binary logs are activated, free disk space is not null; [EMAIL PROTECTED]:~# ls -l /repl/mysql/data/master.* -rw-rw 1 mysql mysql 1814256 2005-02-08 11:43 /repl/mysql/data/master.01 -rw-rw 1 mysql mysql 16 2005-02-07 22:28 /repl/mysql/data/master.index [EMAIL PROTECTED]:~# df -h /repl/mysql/data/ Sys. de fich. Tail. Occ. Free. %Occ. Monté sur /dev/hd0/repl04,0G 607M 3,4G 15% /repl0 the correct grant has been typed on the master server to allow the slave to connect and replicate; (launched with --skip-name-resolve) mysql grant replication slave, file on *.* to [EMAIL PROTECTED] identified by 'pass'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql flush privileges; Query OK, 0 rows affected (0.00 sec) now I change the master to, and ask for the master (in production), to give his data to the slave; mysql change master to MASTER_HOST='db0', MASTER_USER='repl', MASTER_PASSWORD='pass', MASTER_LOG_FILE='master.01'; Query OK, 0 rows affected (0.03 sec) the only thing I can get is: mysql load data from master; ERROR 1189 (08S01): Net error reading from master Any ideas ? is this a bug in 4.1.8 ? Regards, Olivier Kaloudoff CKR Solutions Open Source Mandelieu, France http://www.ckr-solutions.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
default values
Howdy all, is it possible to force a field to be NOT NULL but not have any default value (I.E the insert statement must explicitly provide data for the field in question)? Cheers, Tripp __ 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: default values
On Tuesday 08 February 2005 03:32 pm, Emmett Bishop wrote: is it possible to force a field to be NOT NULL but not have any default value (I.E the insert statement must explicitly provide data for the field in question)? of course: col1 int not null Jeff pgpKRoQG6Ktfo.pgp Description: PGP signature
mysqld process hangs, cannot terminate
Hello everyone, we are having a problem on our server with the mysqld
process hanging. We first notice the problem because our max
connections is reached. A show processlist query shows 250 queries
with a status of locked.
When we try to issue the command: mysqladmin -u root -p shutdown, it
does not work and locks up. When we view a list of processes, we see
that mysqld has been running very long (over 1 hour, the time our site
stops working) and continues to run. Nothing we do will terminate that
process (we have
tried: kill pid, kill -s 9 pid, everything that is supposed to
forcibly kill the process and nothing works).
If we switch to the MySQL data directory and do a directory listing
(to try and investigate the log files), this process also freezes. I'm
just including this info because raise a flag for someone.
Ultimately, we are forced to reboot the machine, and of course check
and repair the entire database (since it was not shut down properly).
Due to the size of the database, this takes 2+ hours, and is obviously
horrible for our site.
Our site is extremely busy (250,000+ queries per day) and has been for
several years. We have never had problems in the past. Is it possible
our server or MySQL just can't handle the load? Meaning, is this the
most likely cause? Or is it more likely that one specific query is
causing the crash?
Could logging have something to do with this (we were using both of
the logs, regular and update, when the crashes occurred - could
disabling them
help?) We use a Cobalt RaQ550 with 1 GB of RAM (which is almost never
completely used) and dual processors (the load balance on the machine
is rarely over 1, let alone 2).
We have one potential cause of the problem. This only seems to occur
when our server is sending emails. Here's an overview of this process:
We use cron to execute a PHP script every few minutes. The script
queries a table we have setup and retrieves details for the mailings
to send (a few hundred per execution). It loops through the result and
sends the email inside the loop. Is it possible that this loop is
somehow not finishing, leaving the result set open, and ultimately
causing mysqld to crash?
Ultimately, the question is, Is it poor practice to execute code
inside a result set loop if there is a possibility of the loop never
finishing?
Sample code below.
Current:
$result = mysql_query(query for list of mailings to send) while ($row
= mysql_fetch_array($result)) {
// do a bunch of stuff
// send the mailing
}
mysql_free_result($result)
Possible better code:
$result = mysql_query(query for list of mailings to send) while ($row
= mysql_fetch_array($result)) {
// add to data type (perhaps array)
}
mysql_free_result($result)
loop through array {
// send mailing here
}
I'm throwing this information out there with absolutely no idea
whether or not this has anything to do with our problem. Any
information would be greatly appreciated!
Hardware: Cobalt RaQ550, 1 GB RAM, dual (fast) proc MySQL version 3.23.38
Thank You,
Brian Erickson
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ERROR 1189 (08S01): Net error reading from master
On Tue, 8 Feb 2005, Bastian Balthazar Bux wrote: Just a shot in the dark do you have skip-networking or bind-address uncommented in your my.cnf ? Reading better what you write I suppose the answer is yes ;) can you connect from one server to the other using the replication user ? no skip-networking or bind to localhost activated. The two servers are clearly speaking to each other, as the first try was refused with : db0 does not allow from 192.168.0.177, and after the grant, I got the error 1189.. (lsof -i has open socket to *:mysql) Additionnaly, I can see clearly that db0 receives the command, as the Query Cache is completely wiped out to 0 Queries when load data from master is issued So there does not seem like a network problem to me :) another idea ? Olivier Kaloudoff ha scritto: Hi, As no one answered to the question below, I'm wondering wether it's a FAQ ... or a bug for which I should fill a report .. My network is ok between the two machines, I can ssh from one to another and any traffic can flow. But this error prevents me to start replication at all.. Olivier On Tue, 8 Feb 2005, Olivier Kaloudoff wrote: Hi, I have some problems here with two mysql servers, version 4.1.8; on the master server, binary logs are activated, free disk space is not null; [EMAIL PROTECTED]:~# ls -l /repl/mysql/data/master.* -rw-rw 1 mysql mysql 1814256 2005-02-08 11:43 /repl/mysql/data/master.01 -rw-rw 1 mysql mysql 16 2005-02-07 22:28 /repl/mysql/data/master.index [EMAIL PROTECTED]:~# df -h /repl/mysql/data/ Sys. de fich. Tail. Occ. Free. %Occ. Monté sur /dev/hd0/repl04,0G 607M 3,4G 15% /repl0 the correct grant has been typed on the master server to allow the slave to connect and replicate; (launched with --skip-name-resolve) mysql grant replication slave, file on *.* to [EMAIL PROTECTED] identified by 'pass'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql flush privileges; Query OK, 0 rows affected (0.00 sec) now I change the master to, and ask for the master (in production), to give his data to the slave; mysql change master to MASTER_HOST='db0', MASTER_USER='repl', MASTER_PASSWORD='pass', MASTER_LOG_FILE='master.01'; Query OK, 0 rows affected (0.03 sec) the only thing I can get is: mysql load data from master; ERROR 1189 (08S01): Net error reading from master Any ideas ? is this a bug in 4.1.8 ? Regards, Olivier Kaloudoff CKR Solutions Open Source Mandelieu, France http://www.ckr-solutions.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]
Merge vs multiple innodb performance
Hi, I'm writing an windows app, which would connect to mysql server and modify user's data. There would be a few hundred of users. Every of them should have access only to few tables. It's not a problem with mysql's authentication mechanism. Kind of compilation of the data from users' tables should be accessed on web (via php scripts). For example: every user have table LOGIN_customers with the same structure. On web, I need to make SELECT (something) FROM LOGIN1_customers, LOGIN2_customers, LOGIN3_customers WHERE (something). I thought, that MERGE tables could be good solution but they don't have transactions and they have limitations. What would be faster? MERGE tables or queries with 200-400 InnoDB tables in FROM? -- Marcin Lewandowski [ mailto:[EMAIL PROTECTED] gg:188068 jid:[EMAIL PROTECTED] ] [ http://www.nosoftwarepatents.com/pl/m/intro/index.html ] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld process hangs, cannot terminate
On Tue, Feb 08, 2005 at 03:57:16PM -0600, Brian Erickson wrote: stops working) and continues to run. Nothing we do will terminate that process (we have tried: kill pid, kill -s 9 pid, everything that is supposed to forcibly kill the process and nothing works). A unkillable process like that is usually the result of a hardware problem. In 'top' is the process stuck on 'D', ie waiting for a disk to respond? -Jason Martin -- If you can't debug it, deplug it. This message is PGP/MIME signed. pgp660FTDZ8an.pgp Description: PGP signature
Re: Merge vs multiple innodb performance
Chuck Herrick napisa(a): 200 - 400 tables is too many. Is it too many for merge, innodb or both? Try having one CUSTOMERS table. You know who is logged in, so you can use that information in a WHERE clause. Yes, but If somebody would find a password (maybe using brute-force attack) to one account, could delete data of other users... -- Marcin Lewandowski [ mailto:[EMAIL PROTECTED] gg:188068 jid:[EMAIL PROTECTED] ] [ http://www.nosoftwarepatents.com/pl/m/intro/index.html ] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problems installing MySql...
I already have a version of MySql 4.1.17 running on a different server, but am trying to setup v.4.0.23 to see if it will allow me to use PHPBB's software. I'm finding that 4.0.23 isn't as easy to set up as the later versions. I modified the my.ini file and put it in the \windows directory for Windows Server 2003. When I try to use the MySql Administator, and put in the server name, root account and password defined in the my.ini file, it doesn't connect, using 'localhost'. What am I doing incorrectly? Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query problem in num_row
Your not getting a valid result from your query. Add
if (!$result) { echo 'Bad query - message: ' . mysql_error();}
I think it will give you a syntax error on your query. MySQL syntax for
the LIMIT clause is:
[quote]
The LIMIT clause can be used to constrain the number of rows returned by
the SELECT statement. LIMIT takes one or two numeric arguments, which
must be integer constants.
With two arguments, the first argument specifies the offset of the first
row to return, and the second specifies the maximum number of rows to
return. The offset of the initial row is 0 (not 1):
mysql SELECT * FROM table LIMIT 5,10; # Retrieve rows 6-15
[/quote]
Respectfully,
Ligaya Turmelle
Aji Andri wrote:
here a syntax
$query=$conn-Execute(select * from itemlocation
where id limit 1 - 50);
$result=mysql_query($query);
$num_result=mysql_num_rows($result);
and error message are syntax error in
$num_result=mysql_num_rows($result); object unknown
can someone please give a direction
all I want to do is comparing data in itemlocation
with a value and if value in item location is smaller
then the value I make then it will appear in a message
Aji
__
Do you Yahoo!?
The all-new My Yahoo! - Get yours free!
http://my.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Multiple table join help
All, I have done some reading and research; however, I seem to be at a loss... And this time, I am not sure how to ask Google... Here is the problem: Table A: id INT valuevarchar (10) Table B: id INT valuevarchar (10) Table C: id INT AINT BINT DINT Table D: id INT ... other_values Table C is in order to normalize values for A and B So, I want to be able to do a query to C that returns the id values of D in order to cross reference that table later. Since table C only knows the numeric values of the string, I need to be able to do so as part of the query by comparing C.a with A.id; however, I also need to compare with the value string... Now, I can do that with one table (A, for example): mysql select C.D from C, A - where C.a = A.id AND A.value='berry'; However, I am not sure how to add the next condition: - where C.b = B.id AND B.value='fruit'; That would allow me to obtain the values on C where A.value = berry and B.value = fruit; however using the numeric values of A.id and B.id There most be a simple solution... To add to the problem, I am using MySQL 4.0.15, so the multiple select would not work... Any help would be appreciated as I have little hair left!!! Beforehand, thank you for the help! __ Do you Yahoo!? Yahoo! Mail - You care about security. So do we. http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqld error
Hi, I am trying to get mysql working on my Windows ME machine. When trying to start mysqld I get the following error message: 050208 19:43(time) [ERROR] C:\Progra~1\mysql\mysql 4.1 Server\bin\mysqld unknown option '--enable-named-pipe' MySQL version 4.1.9. Any help greatly appreciated. Dick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT DISTINCT Problem
I have a large database that I am trying to run a SELECT DISTINCT across. SELECT DISTINCT Date FROM tb_spots WHERE aired_station = '??' However the results I am getting from this query do not match up with the data on the database, instead there are large gaps. Is there any know problem with SELECT DISTINCT across large databases? -- James Purser Winnet Developer +61 2 4223 4131 http://www.winnet.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Permission Denied for INSTALL-BINARY
Hi All, using Fedora2 and trying to install from a .tar file. Extracted to this dir, mysql-standard-4.1.9-pc-linux-gnu-i686 where I see the INSTALL-BINARY file. Using this cmd, ./INSTALL-BINARY gives the following error, [EMAIL PROTECTED] mysql-standard-4.1.9-pc-linux-gnu-i686]# ./INSTALL-BINARY -bash: ./INSTALL-BINARY: Permission denied What am I doing wrong here.? I'm rather new to Linux too. Cheers. Mark Sargent. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Copying Database from One Hard Drive to Another
I've been using a preconfigured package (Apache, PHP, MySQL) from Apache Friends/XAMPP for several months. About a week ago, my computer crashed, and I'm now getting back on my feet. I downloaded the latest XAMPP, which features upgrades for PHP, phpMyAdmin and I think MySQL, too. Everything seems to be working fine, and I'm ready to retrieve my database, which contains over 100 tables. I copied the original XAMPP, with all my databases into an external hard drive. I can see my databases at the following location: MySQL data my_database But before I do anything stupid, I'd like to ask what's the correct way to proceed? I assume I can simply copy the folder my_database and paste it into MySQL data on my new XAMPP setup, right? Will the usernames and passwords be installed with it, or will I have to recreate them? Or is it better to go into my current XAMPP/MySQL, create a database with the same name and assign it the proper passwords, then paste my tables inside it? I assume either method will work, but I just want to make sure. 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]
