Re: InnoDB database Lost
Hopefully you're not still having this problem. I don't use phpMyAdmin, but I know that it allows you to run "repair table" to try to fix a table after a crash. Does that work? -Sheeri On 6/25/06, Khaled Jouda <[EMAIL PROTECTED]> wrote: Hello, I am having a problem with one of my MySQL databases, the server was crashed, and then all InnoDB tables seem to be empty, when I click any innoDB table name in PhpMyAdmin i get the following error: #1016 - Can't open file: 'forums.ibd' (errno: 1) when I click the database name, I get a list of the tables, where PhpMyAdmin writes "in use" under the following columns: Records,Type,Collation, and size Do you have any idea why such a thing happens, and what can be done to restore the database? thanks Khaled -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: create view and insert into problems
create view v_authornames as
select authorid, CONCAT(lastname,',',firstname)
from t_authors;
Or replace the middle term in the CONCAT function to whatever you want
to separate it -- ie, ' ' for a space, or just
CONCAT(lastname,firstname) to get output "KritzerSheeri".
-Sheeri
On 6/25/06, Andreas Bauer <[EMAIL PROTECTED]> wrote:
Hello NG,
I have two tables in my mysql database
created with phpmyadmin:
t_authors:
1 authorid (primary key, auto_increment)
2 lastname
3 firstname
And a table named t_books, fields inside:
t_books:
1 bookid (primary key, auto_increment)
2 authorid (Typ:index, reference to authorid from t_authors done
with phpmyadmin)
3 title
4 subtitle
Now I want to create a view from t_authors, so that the fields
of lastname and firstname are one field with
the value inside: "lastname, firstname":
create view v_authornames as
select authorid, lastname || ', ' || firstname
from t_authors;
But this view created only an empty field named
lastname ||', ' firstname. How can I join this two
fields so that I get one and this value?
There is another problem of me inserting values sequently in
the two tables:
insert into t_authors (lastname, firstname)
values ('Meyers', 'Scott');
insert into t_books (authorid, title, subtitle)
values ('1'), 'Effektiv C++ Programmieren',
'50 Wege zur Verbesserung Ihrer Programme und Entwuerfe');
insert into t_books (authorid, title, subtitle)
values ('1'), 'Mehr Effektiv C++ Programmieren',
'35 neue Wege zur Verbesserung Ihrer Entwuerfe und Programme');
insert into t_authors (lastname, firstname)
values ('Schlossnagle', 'George');
insert into t_books (authorid, title, subtitle)
values ('1'), 'Advanced PHP Programming', 'A practical guide');
The problem is the authorid of t_books: which value should I take for
authorid of table t_books.
If I took '1', the referenced value of the authorid from t_authors was not
taken from the authorid from t_books, but always the value '1'?
If I took '0' or others I get errors.
In phpmyamin I set the reference to t_authors.authorid in the
t_books.authorid field.
Best regards and many thanks
Andreas
--
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: Importing large data sets
On 6/22/06, Scott Haneda <[EMAIL PROTECTED]> wrote: I have two chunks of data to import, one is in this format: "01001 - AGAWAM, MA","01001",0,0,291,249,0,"42.070206","-72.622739" Where it is comma sep and partially quoted The other is in this format "99502 ANCHORAGE, AK","256","265","1424","1962","1131","528","643","6209","99502","61.096163", "-150.093943" Where everything is in quotes and comma sep Can someone tell me, how to rapidly import all this data into a table. Check out mysqlimport: http://dev.mysql.com/doc/refman/5.0/en/mysqlimport.html particularly the --fields-optionally-enclosed-by and --fields-terminated-by options. I'm sure it's too late for you, but you do NOT need to edit things in a text editor. -Sheeri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to RESET @@session.error_count system variable
As a session variable, it resets when you open a new session. -Sheeri On 6/21/06, Tony_10ph <[EMAIL PROTECTED]> wrote: Hello guys... I have stored procedures and I want when a calling program call my stored procedure it will return a response that the stored procedure execute successfuly or return an error code to the calling program. I found a @@session.error_count system variable but if theres an error this variable store error count occur. My problem is I can't RESET the value of the @@session.error_count variable coz it says this is a read only variable. Or is there any way functions I can use to handle error?. Tony -- View this message in context: http://www.nabble.com/How-to-RESET-%40%40session.error_count-system-variable-t1827840.html#a4986142 Sent from the MySQL - General forum at Nabble.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: intended behavior of host %
Better late than never I believe the reason is because (at least on unix) if you connect to a database on the same machine (using -h localhost or just omitting the host) it will use the socket -- and therefore saying "host=localhost" is like saying "if they come from the unix socket", where as saying "host=IP" or "host=name" is like saying "if they come from the port and match the host". It's extremely frustrating; in a heterogenous OS environment it can be even worse. Machines with more than one A record don't always show up the same way when they're connecting, so you can't just specify host=IP or host=name, but have to specify something like host=xxx.xxx.% or host=%.domain.com (or both, since I have no idea what controls whether or not the connection is seen as coming from ip or host -- I believe it's OS specific). It stinks, but we can wait until LDAP authentication comes alongsee http://dev.mysql.com/tech-resources/faq.html#sec3 "SEC 3. Does MySQL 5.0 have built-in Authentication against LDAP directories? No. Support for external authentication methods is on the MySQL roadmap as a "rolling feature". This means that it is not a flagship feature, but will be implemented, development time permitting. Specific customer demand may change this scheduling. " -Sheeri On 6/20/06, John Bishop <[EMAIL PROTECTED]> wrote: Is there a reason that '%' doesn't match 'localhost'? I recently spent more time than I care to admit tearing my hair out over this while setting up authorization for a user. I've looked at the link that James provided, which does at least mention this inconsistency, but it doesn't seem to give a reason for it. Anyone? James Barros wrote: > Never mind. > > I'll just go RTFM and quit bugging you guys on list with stuff > answered plain as day in > http://dev.mysql.com/doc/refman/5.1/en/adding-users.html > > Sorry. I'll go caffeinate myself before asking more stupid questions. > > On Jun 14, 2006, at 11:55 AM, James Barros wrote: > >> Hey guys, I'm running mysql 5.1.9 and I've got a user who's >> mysql.user host is set to "%" and can log in from any domain except >> localhost. if I change to localhost, and flush privileges they can >> log in. >> >> Is this intended behavior, or should % be inclusive of localhost? >> > -- John Bishop -- [EMAIL PROTECTED] Lone Star Internet -- +1 512 708-8006 -- http://lone-star.net -- 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: Warnings while trying to restore database
(apologies for the late reply). 1) A warning doesn't stop the rest of the script from running. Temporary tables go away at the end of the session, and aren't visible to any other session, so the server not being able to handle those commands is no big deal. 2) I'm using MySQL 5.0.19-standard-log on linux and I cannot reproduce your warning -- I put the create temporary table statements into a file, and ran it on the OS commandline (using mysql < file) as well as on the MySQL commandline (using source file;). In both cases, the script ran just fine, no errors or warnings. -Sheeri On 6/15/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: I'm trying to restore a MySQL database in v5.0 (that minor number is in the teens, I don't have it at hand). I get a bunch of warnings like: Warning: Do not know how to handle this statement at line 28: CREATE TEMPORARY TABLE `CHARACTER_SETS` ( `CHARACTER_SET_NAME` varchar(64) NOT NULL default '', `DEFAULT_COLLATE_NAME` varchar(64) NOT NULL default '', `DESCRIPTION` varchar(60) NOT NULL default '', `MAXLEN` bigint(3) NOT NULL default '0' ) ENGINE=MEMORY DEFAULT CHARSET=utf8; Ignoring this statement. Please file a bug-report including the statement if this statement should be recognized. Warning: Do not know how to handle this statement at line 86: CREATE TEMPORARY TABLE `COLLATIONS` ( `COLLATION_NAME` varchar(64) NOT NULL default '', `CHARACTER_SET_NAME` varchar(64) NOT NULL default '', `ID` bigint(11) NOT NULL default '0', `IS_DEFAULT` varchar(3) NOT NULL default '', `IS_COMPILED` varchar(3) NOT NULL default '', `SORTLEN` bigint(3) NOT NULL default '0' ) ENGINE=MEMORY DEFAULT CHARSET=utf8; Ignoring this statement. Please file a bug-report including the statement if this statement should be recognized. I've searched the list archive and the bug database without finding a clue. What's this about? How do I work around it? -- 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: transaction isolation level
Apologies for a late reply.
1) The query that tries to insert the invalid entry into Table2 fails.
Therefore, if you have 3 separate queries as in the first case, the
last one fails, but the first 2 are successful. In the second case,
they're all in one query, and if one fails, they all fail.
For optimization purposes, MySQL doesn't turn a query in extended
insert syntax into multiple queries. The whole point of extended
insert is that it batch processes inserts faster than individual
inserst.
2) Sure, there are plenty of ways. Look up IF(), user variables, and
most importanly, ROLLBACK. Basically, you want to find out if the
inserts were successful, and if any one insert wasn't successful, you
rollback your transaction. Your example never actually has a decision
point where you decide whether or not to commit or rollback.
3) There is no way to figure out which value to be inserted made an error.
On 6/14/06, Konrad Baginski <[EMAIL PROTECTED]> wrote:
Hi.
I have a few questions regarding the transaction levels in mysql 5.0.20
using InnoDB tables.
we are trying to populate two tables in the two following ways, we thought
that they would be equivalent, apparently they are not.
have a look at the following (questions last).
FIRST METHOD:
create database test10;
use test10;
DROP TABLE IF EXISTS Table2;
DROP TABLE IF EXISTS Table1;
CREATE TABLE Table1 (
id BIGINT NOT NULL AUTO_INCREMENT,
logid VARCHAR(32) NULL,
PRIMARY KEY(id),
UNIQUE KEY log_id_key(logid)
)ENGINE=InnoDB;
DROP TABLE IF EXISTS Table2;
CREATE TABLE Table2 (
id BIGINT NOT NULL AUTO_INCREMENT,
table1id BIGINT,
PRIMARY KEY(id),
FOREIGN KEY (table1id) REFERENCES Table1(id) ON DELETE CASCADE
)ENGINE=InnoDB;
START TRANSACTION;
INSERT INTO Table1(logid) VALUES('1');
INSERT INTO Table1(logid) VALUES('2');
COMMIT;
START TRANSACTION;
INSERT INTO Table2(table1id) VALUES('1');
INSERT INTO Table2(table1id) VALUES('2');
INSERT INTO Table2(table1id) VALUES('3');
COMMIT;
select * from Table1; select * from Table2;
++---+
| id | logid |
++---+
| 1 | 1 |
| 2 | 2 |
++---+
2 rows in set (0.00 sec)
++--+
| id | table1id |
++--+
| 1 | 1|
| 2 | 2|
++--+
2 rows in set (0.00 sec)
### END FIRST METHOD ###
SECOND METHOD:
create database test10;
use test10;
DROP TABLE IF EXISTS Table2;
DROP TABLE IF EXISTS Table1;
CREATE TABLE Table1 (
id BIGINT NOT NULL AUTO_INCREMENT,
logid VARCHAR(32) NULL,
PRIMARY KEY(id),
UNIQUE KEY log_id_key(logid)
)ENGINE=InnoDB;
DROP TABLE IF EXISTS Table2;
CREATE TABLE Table2 (
id BIGINT NOT NULL AUTO_INCREMENT,
table1id BIGINT,
PRIMARY KEY(id),
FOREIGN KEY (table1id) REFERENCES Table1(id) ON DELETE CASCADE
)ENGINE=InnoDB;
START TRANSACTION;
INSERT INTO Table1(logid) VALUES('1'), ('2');
COMMIT;
START TRANSACTION;
INSERT INTO Table2(table1id) VALUES('1'), ('2'), ('3');
COMMIT;
select * from Table1; select * from Table2;
++---+
| id | logid |
++---+
| 1 | 1 |
| 2 | 2 |
++---+
2 rows in set (0.00 sec)
Empty set (0.00 sec)
### END SECOND METHOD ###
Questions
1. Why are the two ways of adding rows not equivalent, after all, they both
happen in a trancation?
2. Is there some way to make both of them either add the two first rows to
Table2 or not to add any row?
3. If we look at the second method to insert values, how can i find out
exacly which of the values made an error?
(in this case, the third value has no matching row in Table1).
/konrad baginski
--
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: report of last transaction?
Yes and no. The binary log is a long list of all changes to the system, so if that's enabled you can get what was done. My binary log (on 4.1.19) shows the query thread, but not the user/host that did the change. So it might be difficult to implement an "undo". Also, "delete from table1" does not actually tell you what the old values were, nor does "update table1 set bar=baz where yar=yaz". So you can tell what changed, but not necessarily how to change it back. Hope that helps a bit. -Sheeri On 5/30/06, Karl Berry <[EMAIL PROTECTED]> wrote: (Excuse the beginner's question, but I couldn't find an answer in the documentation or in searching online. Please cc me on replies if possible.) Does mysql have a way to report on the last committed change to the database? I gather there is no undo in databases, in the sense of an editing undo, and I gather rollback applies before a final commit is done. I'm interested in the simpler case where the user makes a change and then says "oops, I think I made a mistake". Thus, having a way to review what was just done (so the user can resubmit if necessary). If there is nothing at the database level, of course I can (attempt to) keep track in my application and report it that way. I just thought it would be easier and more reliable to get the info from the db, if it's available. Thanks, Karl ([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: name 'Szczech' returns more rows then 'Szczec%'
Did you try the first query again? Is it possible that another
"Szczech" was added in the time you did both queries? What happens
when you the simple
mysql> select id_c, name, from clients where name like 'Szczec%';
and
mysql> select id_c, name, from clients where name like 'Szczech';
and most importantly
mysql> select id_c, name, from clients where name = 'Szczech';
The answers to those will help figure out what's going wrong.
-Sheeri
On 5/31/06, Lukasz Budnik <[EMAIL PROTECTED]> wrote:
Hi All,
I'm using 5.0.21 on Gentoo.
I've got very strange problem.
I have clients table with id_c and name columns.
I'm running simple selects:
mysql> select id_c, name, concat('#',name, '@'), length(name) from clients
where name like 'Szczec%';
+---+--+---+--+
| id_c | name | concat('#',name, '@') | length(name) |
+---+--+---+--+
| 30181 | Szczech | #Szczech@ |7 |
+---+--+---+--+
1 row in set (0.00 sec)
mysql> select id_c, name, concat('#',name, '@'), length(name) from clients
where name like 'Szczech';
+---+--+---+--+
| id_c | name | concat('#',name, '@') | length(name) |
+---+--+---+--+
| 30181 | Szczech | #Szczech@ |7 |
| 30693 | Szczech | #Szczech@ |7 |
+---+--+---+--+
2 rows in set (0.00 sec)
ups... what's going on?
% stands for (m)any character(s) but returned 1 row,
when % replaced with 'h' the same query returned 2 rows.
I'm using latin2 charsets but 'h' is standard latin1 character.
Any ideas?
thanks in advance for any help
best regards
Lukasz Budnik
--
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: Problem in using LOAD DATA LOCAL INFILE
You need to check your syntax. You probably don't have FIELDS TERMINATED BY and [OPTIONALLY] ENCLOSED BY and ESCAPED BY set correctly. http://dev.mysql.com/doc/refman/5.0/en/load-data.html -Sheeri On 5/31/06, Neeraj <[EMAIL PROTECTED]> wrote: Hi harish Thanks for reply By executing this query for this table I am not getting any error.. But I am getting wrong result. I have tried many combinations for LOAD DATA LOCAL INFILE but I am getting the results in two ways... 1. all the data getting loaded in first columns of first row. Or 2. data for all columns getting loaded in first column only and getting error line now data for other columns Hope you will find the solution Cheers.:) Neeraj Black Bits -Original Message- From: Harish TM [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 30, 2006 10:23 PM To: sheeri kritzer Cc: Neeraj; mysql@lists.mysql.com Subject: Re: Problem in using LOAD DATA LOCAL INFILE I went through a lot of trouble with Load data local infile... There are a lot of things regarding Local that MySql does not allow due to security reasons. As mentioned before this you could give us the error... that might help alternatively try out mysqlimport http://dev.mysql.com/doc/refman/5.0/en/mysqlimport.html Its similar but a lot easier to use. However its a program so embedding it in code is not efficient.. hope this helps harish On 5/30/06, sheeri kritzer <[EMAIL PROTECTED]> wrote: > > What's the error you are getting? What MySQL version are you using? > > On 5/26/06, Neeraj <[EMAIL PROTECTED]> wrote: > > Hi all > > > > > > > > I have a table with following structure > > > > > > > > CREATE TABLE `dsc` ( > > > > `ch_code` varchar(255) default NULL, > > > > `name` varchar(255) default NULL, > > > > `inof` blob > > > > ) ENGINE=InnoDB DEFAULT CHARSET=latin1; > > > > > > > > And I have give a text file with following format > > > > > > > > Ü > > > > ¢ > > > > Ch.Code :02Ü > > > > MEATÜ > > > > > > > > 1 COUNTRY: JORDAN > > > > > > 2 COUNTRY: SAUDI ARABIA > > > > > > > > > > > > Ü > > > > ¢ > > > > Ch.Code :03Ü > > > > FISHÜ > > > > > > > > 1 COUNTRY: JORDAN > > > > > > > > > > Ü > > > > ¢ > > > > Ch.Code :04Ü > > > > FISHÜ > > > > > > > > 1 COUNTRY: JORDAN > > > > > > 2 COUNTRY: SAUDI ARABIA > > > > > > > > ... > > > > > > > > I am trying to import the text file into table. I have tried various > ways > > but unable to find the solution. the code I am trying to import the file > is > > as follows > > > > > > > > LOAD DATA LOCAL INFILE 'd:\\data3.txt' INTO TABLE dsc FIELDS TERMINATED > BY > > 'Ü\n' LINES TERMINATED BY '¢\n' (ch_code, name, info); > > > > > > > > Can any one help me in loading this file… > > > > > > > > Regards > > > > > > > > Neeraj Black Bits > > > > > > > > -- > 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: Binding mysql to more than 1 ip address but not to all
People who've asked the same question have gotten the answer that it's not possible with MySQL, if I'm remembering correctly. -Sheeri On 5/29/06, Ghaffar <[EMAIL PROTECTED]> wrote: Hello all, I have seen that there are some people asking the same question. How to bind to multiple ip addresses but not to all? I have the need to do the same. I am running multiple Mysql servers on a cluster (Not mysql cluster). The cluster has 2 nodes and they are connected to a SAN to get disks for each database instance. There are around 4-5 instances running on the cluster binding to a specific address. For example 192.168.100.n1 192.168.100.n2 192.168.100.n3 etc The 192.168.100 is the network which connects to all production hosts/switches and I dont want to saturate the traffic on this network. I have another 192.168.50 network which connects to backup switches. Here is all the traffice related to backup aggregation. I would like to back the mysql servers over the backup network. For example the server that listens to 192.168.100.110, I would like to connect to 192.168.50.110 etc. Okay, so from the source it seems that this is not possible. Would anyone advice using tricks with netfilter to be able to do this? Or. Would it be very difficult to patch mysql to listen to a list of ip addresses? Thanks and best regards. Atif -- 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: LOAD DATA FROM MASTER stops unfinished with Query OK
If it worked on your test box, but not in production, what is different about those 2 boxes? Common sense says "If it worked on one box but not another, it's not the software, but a difference between the 2 boxes." And you can always file a bug report with MySQL or get a consultant (through MySQL AB or not) to help you. This list isn't the entirety of knowledge out there on MySQL, although it's pretty good. -Sheeri On 5/29/06, Bgs <[EMAIL PROTECTED]> wrote: It seems nobody has a clue here :( I've given up with MySQL replication... Hope it will work in 5.1 ... BTW: Any official info or estimate about the production release? Bgs wrote: > Nope... pure myisam... > > sheeri kritzer wrote: > >> yeah, I'd be willing to guess that you're mostly innodb. LOAD DATA >> FROM MASTER only works for MYISAM. >> >> http://dev.mysql.com/doc/refman/5.0/en/load-data-from-master.html >> >> -Sheeri >> >> On 5/24/06, Dan Trainor <[EMAIL PROTECTED]> wrote: >> >>> Bgs wrote: >>> > >>> > No ideas? >>> > >>> > I tried playing around with read/write timeouts (even thought the >>> > replication is fast), all size limits are greater than the whole >>> > replicated db. The last table with accesses MYD and zero size is a >>> small >>> > one (a couple of dozens kBs). >>> > >>> > Bgs wrote: >>> > >>> >> >>> >> Greetings, >>> >> >>> >> I played around with load data from master (ldfm) and it worked fine >>> >> in test environment. Now I want to replicate our actual db to a >>> slave. >>> >> When I issue the ldfm command, it starts the replication. I get Query >>> >> OK, but only about 5% of the db is replicated. Apparently all tables >>> >> that are on the slave in the end are exact copies of the master >>> >> tables, but most MYD files are zero sized >>> >> >>> >> >>> >> Any ideas? >>> >> >>> >> Thanks in advance >>> >> Bgs >>> >> >>> >> >>> > >>> >>> Hi - >>> >>> Which storage engine are you using for the tables or database which >>> you're trying to replicate? >>> >>> Thanks >>> -dant >>> >>> -- >>> MySQL General Mailing List >>> For list archives: http://lists.mysql.com/mysql >>> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] >>> >>> >> > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Running Two Different Versions of MySQL
http://dev.mysql.com/doc/refman/5.0/en/multiple-servers.html -Sheeri On 5/29/06, Michael Monaghan <[EMAIL PROTECTED]> wrote: Hi, I have a machine [Solaris 9 x86] that has MySQL v3.22.xx installed. I am not familiar with the applications [quite a few] that use this instance. I need to install an application that requires a newer version of MySQL. I plan to install v5.x. separately - rather than upgrade the old version. What sort of gotchas/possible conflicts should I be aware of? Port numbers, config files etc.? Thanks, ~mm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem in using LOAD DATA LOCAL INFILE
What's the error you are getting? What MySQL version are you using? On 5/26/06, Neeraj <[EMAIL PROTECTED]> wrote: Hi all I have a table with following structure CREATE TABLE `dsc` ( `ch_code` varchar(255) default NULL, `name` varchar(255) default NULL, `inof` blob ) ENGINE=InnoDB DEFAULT CHARSET=latin1; And I have give a text file with following format Ü ¢ Ch.Code :02Ü MEATÜ 1 COUNTRY: JORDAN 2 COUNTRY: SAUDI ARABIA Ü ¢ Ch.Code :03Ü FISHÜ 1 COUNTRY: JORDAN Ü ¢ Ch.Code :04Ü FISHÜ 1 COUNTRY: JORDAN 2 COUNTRY: SAUDI ARABIA ... I am trying to import the text file into table. I have tried various ways but unable to find the solution. the code I am trying to import the file is as follows LOAD DATA LOCAL INFILE 'd:\\data3.txt' INTO TABLE dsc FIELDS TERMINATED BY 'Ü\n' LINES TERMINATED BY '¢\n' (ch_code, name, info); Can any one help me in loading this file… Regards Neeraj Black Bits -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [solved] Re: grant modify, doc on grant.
Firstly, I apologize for my incorrect db level grant in the syntax I posted. Secondly, by going to mysql.com/grant I found the GRANT syntax page, which states: "The FILE, PROCESS, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SHOW DATABASES, SHUTDOWN, and SUPER privileges are administrative privileges that can only be granted globally (using ON *.* syntax)." -Sheeri On 5/30/06, Gilles MISSONNIER <[EMAIL PROTECTED]> wrote: Thanks Dilipkumar, the syntax works fine mysql> grant file on *.* to 'wr'@'localhost'; so the "file" privilege is for ALL databases. by the way, to allow "alter", the syntax is like : mysql> grant alter on dr4.* to 'wr'@'localhost'; here the "alter" privilege is specific to a database, on a column in a database. If this is explained in the doc, I would like to know which keyword I should give to have a fast answer, through "Search the MySQL manual:" > You can try this option by > > grant file on *.* to [EMAIL PROTECTED] identified by 'db123'; > Query OK, 0 rows affected (0.03 sec) > > For all the Databases. > == Gilles Missonnier IAP - [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: grant modify, doc on grant.
GRANT FILE ON dr4.* to 'wr'@'localhost' IDENTIFIED BY 'the_passwd'; It won't set up a new account, just add the privilege for you. -Sheeri On 5/26/06, Gilles MISSONNIER <[EMAIL PROTECTED]> wrote: Hello How to set "FILE privilege enable" to an already defined user ? It seems that I have to read the all manual for that. I cannot find an example in the on line manual. thanks. - Gilles - - I will end up running mysql as root. 1-I set up an user mysql> GRANT SELECT, INSERT, UPDATE, CREATE ON dr4.* to 'wr'@'localhost' IDENTIFIED BY 'the_passwd'; 2- It works fine : mysql> select my_item from my_table ; give it fine. 3- Now I want to select and put the results into a file : mysql> select my_item from my_table into outfile'/tmp/my_outfile'; ERROR 1045 (28000): Access denied for user 'wr'@'localhost' (using password: YES) 4- So I go the online doc , look for "outfile" keyword and I find that I must have FILE privilege enabled. So on the online doc, I look for the syntax for "FILE privilege enable" 5- hu then I decide to run mysql client as root (of mysql) the same command as above (-3) works fine A note to the manual writer : If you want that more people use database, do not make tons : just a tree-like doc. otherwise most people will continue to consider that grep into a file is fine for "database". =_==_==_==_==_==_= =¯==¯==¯==¯==¯==¯= Gilles Missonnier IAP - [EMAIL PROTECTED] 01 44 32 81 36 -- 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: Fun with Dates and Incentives.
Brian, The scenario you list, that a person on the east coast and a person on the west coast will submit tests in 3 hours apart yet show up in the db as having been done at the same time, will not happen. No matter what timestamp you use on the server, UTC or otherwise, it's going to insert the server time into the database. If your server is in PST and a person in EST submits a test at 3 pm, it will look in the database as if it was submitted at 12 noon PST. or 8 pm GMT. Whatever. Time is time, and no matter what format you use, the server records the time it currently is. -Sheeri On 5/25/06, Brian Menke <[EMAIL PROTECTED]> wrote: Thanks Sheeri, but now I'm a bit more confused. Does that mean that the scenario that I listed below does not work even if I use UTC_TIMESAMP? That's what I still don't get, if someone on the east coast submits their test, and the web code on my server (which is on the west coast) is what actually performs the INSERT using UTC_TIMESTAMP, then it seems like this still doesn't work. This is driving me crazy :-) Any ideas? Is the bottom line that I have to have a specific time zone for each student in my database so I can do comparisons that way? I'm thinking about webex and how they send emails that automatically adjusts for time zones. I think at some point, I had to tell them what time zone I was in. I see that a lot on the web. Seems like a pain, but I don't see any way around it. -Brian -Original Message- From: sheeri kritzer [mailto:[EMAIL PROTECTED] Sent: Thursday, May 25, 2006 9:13 AM To: Brian Menke Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: Fun with Dates and Incentives. Brian, MySQL's timestamp function is based on the *server*'s timestamp, not the client's. -Sheeri On 5/24/06, Brian Menke <[EMAIL PROTECTED]> wrote: > Wow, if I read that right, it means someone on the East coast submits > answers to a test, it's somehow adjusted to be the same as someone who > submits answers to a test from the west coast 3 hours later (time zone > wise)? I can't possibly imagine how that works, but if it does that solves > huge problems for me and I seriously owe you! I'm gonna do some more > research so I understand how this works. > > > > THANKS! > > > > -Brian > > > > _ > > From: Peter Brawley [mailto:[EMAIL PROTECTED] > Sent: Wednesday, May 24, 2006 3:56 PM > To: Brian Menke > Cc: mysql@lists.mysql.com > Subject: Re: Fun with Dates and Incentives. > > > > Brian, > > CURRENT_TIMESTAMP gives you time in your server's timezone. UTC_TIMESTAMP > gives GM (universal) time, so dispenses with all timezone adjustments. > Trouble is, you cannot use it as a defalt. You have to pass it as an INSERT > value. > > PB > > - > > Brian Menke wrote: > > Peter, thanks for the detailed info. I will figure out how to get rid of the > UNIQUE key. Somehow that got added. Thanks for the catch. As far as INT for > student id goes, I'm using email because it will be unique, and offers an > easy way to track a user through the app I'm building (user name, password, > session id's etc.) but I do get what you are saying. Thanks for the > UTC_TIMESTAMP suggestion. Although, since I haven't had a lot of experience, > I don't really understand why it is better than CURRENT_TIMESTAMP. But. > that's why I asked for advice :-) > > > > -Brian > > > > _ > > From: Peter Brawley [mailto:[EMAIL PROTECTED] > Sent: Wednesday, May 24, 2006 1:31 PM > To: Brian Menke > Cc: mysql@lists.mysql.com > Subject: Re: Fun with Dates and Incentives. > > > > Brian > > Re your schema, > --it's redundant to define PRIMARY and UNIQUE keys on the same column, > --why not an INT student id? > --what if two (eg married) students share an email account? > --comparing datetimes across multiple time zones will be simpler if you > set completed_modules.time=UTC_TIMESTAMP in each new row of that table. > > That would give ... > > CREATE TABLE students ( > id INT NOT NULL,-- auto_increment [simplest] or assigned by > school? > email varchar(64) NOT NULL, > fname varchar(32) NOT NULL, > lname varchar(32) NOT NULL, > role char(2) NOT NULL default '5', > password varchar(8) NOT NULL, > phone varchar(24) default NULL, > reg_date date default NULL, > PRIMARY KEY (id), > KEY email (email) > ) ENGINE=InnoDB DEFAULT CHARSET=latin1; > > CREATE TABLE completed_modules ( > id INT NOT NULL, > module_id char(2) NOT NULL default '', > score INT NOT NULL default 0, > time timestamp NOT NULL default CURRENT_TIMESTAMP > ) ENGINE=InnoDB DEFAULT CH
Re: errno24 - open_files_limit
If I remember correctly from the certification guide, MySQL opens a file handle for each table opened. When you have a MyISAM table, that's one file handle each for the structure, data and index files. So if you have a query that uses only one table, it can spawn up to 3 file handles.and it's multiplied for multiple connections and joins. Talk to the sysadmin managing the MySQL box on the system about what the current setting is and s/he thinks it should be. -Sheeri On 5/24/06, mel list_php <[EMAIL PROTECTED]> wrote: Hi! We have that huge query against a mysql 4.1 (myisam) to retrieve rows. The query is built dynamically using a loop, as we are searching in an external database built to store a tree with the nested tree model. When we do this search with a "normal" world, it's fine: the query is around 4 lines. If we search with a very "frequent" word, we get a lot of hits, and the query becomes very long (probably around 300 lines), and we get the errno24 Looking at the doc: http://dev.mysql.com/doc/refman/4.1/en/not-enough-file-handles.html so apparently I don't have enough file handlers. Ok, don't mind increasing that.They say to modify the open_files_limit variable. #open_files_limit The number of files that the operating system allows mysqld to open. This is the real value allowed by the system and might be different from the value you gave using the --open-files-limit option to mysqld or mysqld_safe. The value is 0 on systems where MySQL can't change the number of open files. This variable was added in MySQL 3.23.20. 2 questions: - any idea of the value I should put there? mysqld is running on a machine I don't manage, what should I ask for? - is it possible to modify this value in the conf file and to reload that file without stopping/re starting the server? Additionnally, any pointer on a doc explaining why and when is mysql opening a new file? Thanks, melanie _ Are you using the latest version of MSN Messenger? Download MSN Messenger 7.5 today! http://join.msn.com/messenger/overview -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql performance / ndb 5.1 performance
What exactly is the performance problem you are seeing? Have you checked to make sure all your memory is being utilized? ie, not just grabbed by MySQL, but actually in use? -Sheeri On 5/24/06, Dan Trainor <[EMAIL PROTECTED]> wrote: Moritz Möller wrote: > Hi Dan, > > there are about 2GB free, so the net size would still be 32 GB. > > The queries are really optimized, >99.9% of all queries can be satisfied > without table scans. > > Well, I guess I have to give NDB a chance, I hope it will help. The only > alternative I come to is to cluster the database on application level (use > server userID%numServers), which would be a [insert favourite non-swear-word > here] lot of work ;) > > Moritz > > > -Original Message- > From: Dan Trainor [mailto:[EMAIL PROTECTED] > Sent: Thursday, May 25, 2006 1:41 AM > To: Moritz Möller; mysql@lists.mysql.com > Subject: Re: mysql performance > > Moritz Möller wrote: > >>Hi list, >> >>we're running some large high-traffic mysql servers, and are currently >>reaching the limit of our machines. >> >>We're using mysql 4.1 / innodb on debian, ibdata is about 35GB. Hardware > > is > >>quad xeon dualcore, 8 GB RAM. Disk-io is nearly zero, limiting factor is >>CPU. >>The queries run very fast (I seldom see a process that's running longer > > than > >>a second), but there are too many of them, I guess. >> >>As far as I know, NDB keeps the whole database in memory, so with indices >>and some mem as reserve, we'd need ~48GB (3x16 or something) in total for >>NDB :( >> >>Does someone know other solutions to this? Is NDB the only storage engine >>supporting clustering? >> >>Thanks in advantage, >> >>Moritz >> >> >> > > > Hi - > > That's quite a large database. I, too, have been dealing with what I > thought was a large database for this new project. Being 2G, it hardly > compares to your database size. > > Keep in mind, however, that a 36G ibdata file does not necessarily mean > that you are using 36G to store data. InnoDB documents from the MySQL > site explain ways to compact these files, possibly shrinking the size of > ibdata files. Another way to get a better idea of how much data you're > actually using is to use the 'SHOW TABLE STATUS' query from within > MySQL. Take the "InnoDB Free:" item under the 'Comment:' column, and > subtract this from the total size of the ibdata file(s). This will give > you a more accurate representation of how much of that ibdata file > you're actually using. I think. (Someone mind correcting me if I'm way > off here?) > > NDB may not be your solution. Even though disk-based storage is > included with NDB in 5.1 and beyond, I'm not too sure how this will > affect the speed of your operations. I suppose it's worth a try, however. > > Please take this advise with a grain of salt, as InnoDB is still quite > new to me, as well. Other things I've found to speed up large databases > are to properly make indexes, and testing them with the EXPLAIN > function. This alone has let me to speed up our operations as much as > 30% in most cases. > > Thanks > -dant > > Hi - Well, go ahead and do that and let us know how it turns out. There's a whole mailing list on cluster. Like I said, 5.1 (don't remember specifically which version) has file-based storage for cluster as an option. Good luck with that. Thanks! -dant -- 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: Fun with Dates and Incentives.
Brian, MySQL's timestamp function is based on the *server*'s timestamp, not the client's. -Sheeri On 5/24/06, Brian Menke <[EMAIL PROTECTED]> wrote: Wow, if I read that right, it means someone on the East coast submits answers to a test, it's somehow adjusted to be the same as someone who submits answers to a test from the west coast 3 hours later (time zone wise)? I can't possibly imagine how that works, but if it does that solves huge problems for me and I seriously owe you! I'm gonna do some more research so I understand how this works. THANKS! -Brian _ From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 24, 2006 3:56 PM To: Brian Menke Cc: mysql@lists.mysql.com Subject: Re: Fun with Dates and Incentives. Brian, CURRENT_TIMESTAMP gives you time in your server's timezone. UTC_TIMESTAMP gives GM (universal) time, so dispenses with all timezone adjustments. Trouble is, you cannot use it as a defalt. You have to pass it as an INSERT value. PB - Brian Menke wrote: Peter, thanks for the detailed info. I will figure out how to get rid of the UNIQUE key. Somehow that got added. Thanks for the catch. As far as INT for student id goes, I'm using email because it will be unique, and offers an easy way to track a user through the app I'm building (user name, password, session id's etc.) but I do get what you are saying. Thanks for the UTC_TIMESTAMP suggestion. Although, since I haven't had a lot of experience, I don't really understand why it is better than CURRENT_TIMESTAMP. But. that's why I asked for advice :-) -Brian _ From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 24, 2006 1:31 PM To: Brian Menke Cc: mysql@lists.mysql.com Subject: Re: Fun with Dates and Incentives. Brian Re your schema, --it's redundant to define PRIMARY and UNIQUE keys on the same column, --why not an INT student id? --what if two (eg married) students share an email account? --comparing datetimes across multiple time zones will be simpler if you set completed_modules.time=UTC_TIMESTAMP in each new row of that table. That would give ... CREATE TABLE students ( id INT NOT NULL,-- auto_increment [simplest] or assigned by school? email varchar(64) NOT NULL, fname varchar(32) NOT NULL, lname varchar(32) NOT NULL, role char(2) NOT NULL default '5', password varchar(8) NOT NULL, phone varchar(24) default NULL, reg_date date default NULL, PRIMARY KEY (id), KEY email (email) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE completed_modules ( id INT NOT NULL, module_id char(2) NOT NULL default '', score INT NOT NULL default 0, time timestamp NOT NULL default CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=latin1; To find the first 10 scores of 100 on a particular module, just ... SELECT CONCAT(s.lname,', ',s.fname) AS Name, c.time, c.score FROM students s INNER JOIN completed_modules c USING (id) WHERE c.module_id = 1 AND c.score = 100 ORDER BY c.time ASC LIMIT 10; PB I'm hoping for some general advice on an approach for the following scenario: I have a customer who wants to put an incentive program in place for students taking learning modules and then completing tests. The concept is simple. Award the first 10 people who complete a test with a score of 100%... that type of thing. Students are allowed to take test more than once. Track each time the student takes the test and show the latest score ect. You get the idea. I have the database tables and relationships already all set up for the tests, but it's the tracking of the dates and times that I don't have and it got me thinking. I need to track down to the day/hour/minute level. Okay, that should be easy (I think). I'm going to need to do a lot of date/time calculations. Would it be best just to have a default of CURRENT_TIMESTAMP set for a TIMESTAMP field? Or, is their something else I should be using? I have limited experience having to munge and crunch date/time info and I want to make sure I have the flexibility to do what I need in the future. The next gotcha I thought up is what about different time zones. Obviously without this consideration, people on the East coast would have an unfair 3 hour advantage over people on the west coast. I guess I can have a time zone field in my student table so I could derive the time difference. Any suggestions on a good time zone approach? Here are my two tables as they stand now. I'm wondering if these are set up in a way to allow me to do all this date time crunching I'm going to need to do in the future? Any suggestions are greatly appreciated :-) CREATE TABLE `students` ( `store_id` varchar(6) NOT NULL, `email` varchar(64) NOT NULL, `fname` varchar(32) NOT NULL, `lname` varchar(32) NOT NULL, `role` char(2) NOT NULL default '5', `password` varchar(8) NOT NULL, `phone` varchar(24) default NULL, `reg_date` date default NULL, PRIMARY KEY (`email`), UNIQU
Re: How do I turn off error checking
Why not? You can run a script that has 1 command, to source another SQL file, and then your script will not die because of errors. What else is going on under the scenes here, to make it so you "can't" do it? source'ing the script is NOT forcing. When you use mysql < file.sql, if there's an error the process stops. When you use source file.sql on the mysql commandline, an individual error does not stop the rest of the commands in the sql file from running. -Sheeri On 5/24/06, Daevid Vincent <[EMAIL PROTECTED]> wrote: As mentioned in the original email, I know about forcing it, but I can't do that in my case. DÆVID > -Original Message- > From: sheeri kritzer [mailto:[EMAIL PROTECTED] > Sent: Wednesday, May 24, 2006 6:27 PM > To: Daevid Vincent > Cc: mysql@lists.mysql.com > Subject: Re: How do I turn off error checking > > If you run a script with > > mysql < script.sql > > then if there's an error, the script dies. > > If you run a script with > > mysql -e "source script.sql" > > Then the script will just spit out an error and move on to > the next command. > > Hopefully that will help. > > -Sheeri > > On 5/24/06, Daevid Vincent <[EMAIL PROTECTED]> wrote: > > Semi related to this, as it appears in my searching that this is > > unfortunately not a supported feature. Is there a flag or > something that I > > can put in my .sql file that will turn OFF any error > checking, execute my > > statements (so that even if one fails, the next one will be > tried), then > > turn it back on again at the end of the file? Sort of like > how you can do > > the "SET FOREIGN_KEY_CHECKS=0;" so something akin to "SET > ERROR_CHECKS=0;" > > or "SET FORCE=1;" > > > > And, yes, I do know that there is a --force option for the > importing, but I > > cannot use that in my case. This is an automated script and > generally I want > > the SQL to fail on any errors b/c then I know the upgrade > is hosed. This is > > a special case where some people got a SQL upgrade (by > hand) and some > > didn't, hence the discrepency between the schemas. > > > > DÆVID > > > > > -Original Message- > > > From: Daevid Vincent [mailto:[EMAIL PROTECTED] > > > Sent: Wednesday, May 24, 2006 12:13 PM > > > To: mysql@lists.mysql.com > > > Subject: How do I add a column only if it doesn't exist? > > > > > > I want to do something like this: > > > > > > if not exists `hotel_page_templates`.`hpt_custom_fields` > > > alter table `hotel_page_templates` add column > > > `hpt_custom_fields` text after > > > `hpt_alternate_username`; > > > > > > > > > ÐÆ5ÏÐ > > > > > > > > > -- > > > MySQL General Mailing List > > > For list archives: http://lists.mysql.com/mysql > > > To unsubscribe: > > > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql-workbench - not working on fc 5.
What does mysql-workbench --sync give you? -Sheeri On 5/25/06, Gregory Machin <[EMAIL PROTECTED]> wrote: I get the following when I try and lauch mysql-workbench . [EMAIL PROTECTED] ~]$ mysql-workbench The program 'mysql-workbench-bin' received an X Window System error. This probably reflects a bug in the program. The error was 'BadMatch (invalid parameter attributes)'. (Details: serial 749 error_code 8 request_code 143 minor_code 5) (Note to programmers: normally, X errors are reported asynchronously; that is, you will receive the error a while after causing it. To debug your program, run it with the --sync command line option to change this behavior. You can then get a meaningful backtrace from your debugger if you break on the gdk_x_error() function.) -- Gregory Machin [EMAIL PROTECTED] [EMAIL PROTECTED] www.linuxpro.co.za www.exponent.co.za Web Hosting Solutions Scalable Linux Solutions www.iberry.info (support and admin) +27 72 524 8096 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqlhotcopy regexp
You should just be doing mysqlhotcopy ...connect-options... --regexp /~^db_name$/ or mysqlhotcopy ...connect-options... --regexp /~^db_name$/ /path/to/new/dir On 5/25/06, MF <[EMAIL PROTECTED]> wrote: Hi, how to write pattern for backup all datases except one? I try this, but not work as expecting. mysqlhotcopy ...connect-options... --regexp /~^db_name$/./.*/ -- 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]
LOAD DATA FROM MASTER stops unfinished with Query OK
yeah, I'd be willing to guess that you're mostly innodb. LOAD DATA FROM MASTER only works for MYISAM. http://dev.mysql.com/doc/refman/5.0/en/load-data-from-master.html -Sheeri On 5/24/06, Dan Trainor <[EMAIL PROTECTED]> wrote: Bgs wrote: > > No ideas? > > I tried playing around with read/write timeouts (even thought the > replication is fast), all size limits are greater than the whole > replicated db. The last table with accesses MYD and zero size is a small > one (a couple of dozens kBs). > > Bgs wrote: > >> >> Greetings, >> >> I played around with load data from master (ldfm) and it worked fine >> in test environment. Now I want to replicate our actual db to a slave. >> When I issue the ldfm command, it starts the replication. I get Query >> OK, but only about 5% of the db is replicated. Apparently all tables >> that are on the slave in the end are exact copies of the master >> tables, but most MYD files are zero sized >> >> >> Any ideas? >> >> Thanks in advance >> Bgs >> >> > Hi - Which storage engine are you using for the tables or database which you're trying to replicate? Thanks -dant -- 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: How do I turn off error checking
If you run a script with mysql < script.sql then if there's an error, the script dies. If you run a script with mysql -e "source script.sql" Then the script will just spit out an error and move on to the next command. Hopefully that will help. -Sheeri On 5/24/06, Daevid Vincent <[EMAIL PROTECTED]> wrote: Semi related to this, as it appears in my searching that this is unfortunately not a supported feature. Is there a flag or something that I can put in my .sql file that will turn OFF any error checking, execute my statements (so that even if one fails, the next one will be tried), then turn it back on again at the end of the file? Sort of like how you can do the "SET FOREIGN_KEY_CHECKS=0;" so something akin to "SET ERROR_CHECKS=0;" or "SET FORCE=1;" And, yes, I do know that there is a --force option for the importing, but I cannot use that in my case. This is an automated script and generally I want the SQL to fail on any errors b/c then I know the upgrade is hosed. This is a special case where some people got a SQL upgrade (by hand) and some didn't, hence the discrepency between the schemas. DÆVID > -Original Message- > From: Daevid Vincent [mailto:[EMAIL PROTECTED] > Sent: Wednesday, May 24, 2006 12:13 PM > To: mysql@lists.mysql.com > Subject: How do I add a column only if it doesn't exist? > > I want to do something like this: > > if not exists `hotel_page_templates`.`hpt_custom_fields` > alter table `hotel_page_templates` add column > `hpt_custom_fields` text after > `hpt_alternate_username`; > > > ÐÆ5ÏÐ > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How do I add a column only if it doesn't exist?
I don't think MySQL can do thatmaybe with some kind of stored procedure If you're using 5.0 or higher, use the INFORMATION SCHEMA to find out if the column exists. If not, use the "show create table" statement and parse it. Why is this a problem, though? (just curious, I'm not sure this would ever come up. I don't do automatic schema changes, always do them manually, so I'm not too sure why you'd be amissunless you're running gobs of servers and some of them have the column and some of them don't.but even then you could run a script that alters the table to add the column and just let it thrown an error if it already exists.) -Sheeri On 5/24/06, Daevid Vincent <[EMAIL PROTECTED]> wrote: I want to do something like this: if not exists `hotel_page_templates`.`hpt_custom_fields` alter table `hotel_page_templates` add column `hpt_custom_fields` text after `hpt_alternate_username`; ÐÆ5ÏÐ -- 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: Finally Working...wow
Rich for what? is SELECT host,user,password FROM mysql.user; showing you cleartext passwords? It shouldn't. -Sheeri On 5/23/06, Rich <[EMAIL PROTECTED]> wrote: Hi folks. Me again. I finally got this all up and running under crypt of 'cleartext'. So, even though I am going to be on the same box as the server, how do I set up an MD5 or password entry? MYSQLCrypt password() MYSQLCrypt password MYSQLCrypt MD5() MYSQLCrypt MD5 I'm not sure if the brackets are needed or not. So if I use password or MD5 encryption in the tables, the value in the field is encrypted. How does this change how I structure things? instead of 'passwordalpha' in the password field, I have to enter 'ff08d88bab6edcf9d730a96418c05358'? I am entering users via my own interface, and I can't seem to get MD5 working with either MD5 or MD5(). Cheers -- 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: Need to replicate my DB on 5 branches into 1 DB at HO - Urgent
Only with MySQL 5.0 is multi-master replication possible. Guiseppe Maxia has a wonderful article about it at: http://www.onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html -Sheeri On 5/22/06, Winanjaya - CBN <[EMAIL PROTECTED]> wrote: Dear Expert, I am very new to MySQL replication, I need to replicate my DB on 5 branches into 1 DB at Head Office. eg. MyDB at Branch A need to be replicated to MyDB at Head Office MyDB at Branch B need to be replicated to MyDB at Head Office MyDB at Branch C need to be replicated to MyDB at Head Office MyDB at Branch D need to be replicated to MyDB at Head Office MyDB at Branch E need to be replicated to MyDB at Head Office FYI, every table has Branch Id, so it won't be duplicate record! I really need advise .. how to do such replication on MySQL 5..what should I do in my my.ini? Any prompt reply would be appreciated ! .. Thanks a lot in advance Regards Winanjaya -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Crypt Functions
http://dev.mysql.com/doc/refman/4.1/en/encryption-functions.html On 5/21/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: Hi Guys, Can somebody enlighten me on AES_CRYPT & AES_DECRYPT functions on MySQL. I noticed that you can mention DES key file in mysqld statup options. how does the AES works? is there any one who used these functions (production systems)? Thanx in Advance, Kosala This message was sent using IMP, the Internet Messaging Program. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb files
Well, you're going to need to state how big a record is, what OS platform you're using, what MySQL version you're using, and exactly what error message you get when you're trying to insert that 5th record. Your my.cnf would help, too. -Sheeri On 5/22/06, Eko Budiharto <[EMAIL PROTECTED]> wrote: hi, I still confuse how store very large database with innodb engine in mysql. I already activate one file per table (that will creates its own file per table .idb file), but I only can store 4 records only. Do you mind if someone can tell me how to store very large database with innodb engine in mysql? - Yahoo! Messenger with Voice. PC-to-Phone calls for ridiculously low rates. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: aha! replication breaking due to duplicate queries
As an addendum to this -- this was solved. The master and slave were out of sync. -Sheeri On 5/19/06, Jeremy Cole <[EMAIL PROTECTED]> wrote: Hi Sheeri, > So I've seen replication break a few times due to duplicate queries. > A few times it was around a server crashing, but I thought perhaps it > was because of the crash. (ie, master sends a query, crashes, and > then tries to send the query again when it comes back up). > > But in the past 16 hours, it's happened twice. Both times, no crash. > No network problems that we know of. No other query problems. > > Therefore, we've deduced it's in the code -- it's trying to insert a > field with a duplicate primary key. I've sent that off to the > developers, who will hit things with sticks. > > However, why does MySQL transfer over DML queries that fail? If they > have an error, shouldn't they not replicate? This seems like a very > large bug to me. It shouldn't, except in *very* limited circumstances (where you've already shot yourself in the foot a few times, generally). I have seen a case quite a few times where the slave "hiccups" and apparently runs the same query twice. Is it possible that this is what you're seeing? Is your system replicating a mix of large (images, maybe) and small (single-row) updates? Regards, Jeremy -- Jeremy Cole MySQL Geek, Yahoo! Inc. Desk: 408 349 5104 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
aha! replication breaking due to duplicate queries
So I've seen replication break a few times due to duplicate queries. A few times it was around a server crashing, but I thought perhaps it was because of the crash. (ie, master sends a query, crashes, and then tries to send the query again when it comes back up). But in the past 16 hours, it's happened twice. Both times, no crash. No network problems that we know of. No other query problems. Therefore, we've deduced it's in the code -- it's trying to insert a field with a duplicate primary key. I've sent that off to the developers, who will hit things with sticks. However, why does MySQL transfer over DML queries that fail? If they have an error, shouldn't they not replicate? This seems like a very large bug to me. -Sheeri (if y'all protest, I'll do some test cases to see if I can replicate on current versions (we're using 4.1.12 as master and server, replicating for a long time with no problems). I'm East Coast so I'm feeling the Friday laziness). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [5] Starting Up General Query Log
That means that your options have no group. Options should go under the program they're intended to be run under, for instance [mysqldump] user=root [mysql.client] user=guest [mysqld] log=/path/to/logfile You want the mysqld program (mysql server) to use the general log, so put it under a section marked [mysqld] in your config file. -Sheeri On 5/19/06, Rich <[EMAIL PROTECTED]> wrote: Why this list goes private I'll never know. I guess that's why I always get two copies. In the errors log: Found option without preceding group in config file: /etc/my.cnf at line: 1 Fatal error in defaults handling. Program aborted /var/log/ does indeed exist root runs mysqld On May 19, 2006, at 12:01 PM, sheeri kritzer wrote: > Yes. idea #1 -- reply all, including the list. > > idea #2 -- what's in the error logs? > > Check that the user that runs mysql has permission to write to the > file and that /var/log exists. Rich Fortnum [EMAIL PROTECTED] Toronto -- 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: [5] Starting Up General Query Log
Yes. idea #1 -- reply all, including the list. idea #2 -- what's in the error logs? Check that the user that runs mysql has permission to write to the file and that /var/log exists. -Sheeri On 5/19/06, Rich <[EMAIL PROTECTED]> wrote: Hi there. I added the my.cnf file (it wasn't there) and put this into it: log=/var/log/myrequests.log Now MySQL won't start. Any ideas? Cheers On May 19, 2006, at 11:35 AM, sheeri kritzer wrote: > put > > log > > or > > log=/path/to/file > > in your config file (my.cnf) and restart the server. To turn it off > you have to take it out of the my.cnf and restart the server. Rich Fortnum [EMAIL PROTECTED] Toronto -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is This A Redundant Info Example?
It depends on what the data is being used for. If you want to know what address an order was shipped to then you need the historical address info. If all you need is the current customer address, because, say, you're sending out holiday cards, then you might not need to keep an old address. Martin's explanation didn't say anything about deleting the data. Your idea isn't a bad one, although I shy away from boolean flags because you have to be careful about the indexes for it. -Sheeri On 5/19/06, Jay Blanchard <[EMAIL PROTECTED]> wrote: [snip] > Well, one reason could be, for example, that the address changes > over time and they want to know what address the customer was > when the order was processed. [/snip] So you would delete an old customer address in favor of a new one? I would rather have an 'active/inactive' column with an accompanying date stamp. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Position in master.info: read or executed master_log_pos?
Replication puts 2 threads on the slave -- 1 to get the information from the master and put it into the relay log, and another to read from the relay log. You pose a good question about master.info -- I honestly don't know, but the read position is either the same as or before the exec position, so I'd put it as the read position. You say it's for recovery, so it probably doesn't matter unless you somehow have another number for the log position. You only get to put one number in CHANGE MASTER TO MASTER_LOG_POS so it's not like you have a choice anyway. stop slave stops both threads. The exec thread doesn't catch up to the read thread. Aren't these things you could have tested yourself? -Sheeri On 5/18/06, Dominik Klein <[EMAIL PROTECTED]> wrote: Hi, for recovery purposes I need to know what exactly is in the master.info file. Especially the log position. Is it Read_Master_Log_Pos or Exec_Master_Log_Pos? Another question: Does "stop slave;" only stop reading the log from master or does it also stop executing the log that has been read already, but not yet executed? Regards Dominik -- 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: Momentary huge replication lag
I suggest writing a very simple shell script to run "SHOW SLAVE STATUS" and output it to a file every 15 seconds, to verify your script is working. I have never seen MySQL give a bad lag time for replication -- it's always been accurate for lag time, or 0, or NULL. As a bonus you could then use the data from the log files and positions to actually calculate how far behind it was, and file a bug report if indeed the server is wonky. -Sheeri On 5/19/06, Martijn van den Burg <[EMAIL PROTECTED]> wrote: > That is weird. If it only lasts a couple of seconds, how are you > monitoring it to find out what the lag time is? I've written a replication monitor script using Perl::POE, which checks replication lag every 15 seconds or so (can't check the exact interval now - weekend has begun here). -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: trigger mysql 5
You have to change the delimiter you use, otherwise MySQL treats the ; as the end of the line and processes the commands. When I tried to run your trigger (with the standard delimiter of ;) I got: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'update trans set trans_status=NEW.trans_status, trans_state=NEW.trans_state wher' at line 5 mysql> END IF; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END IF' at line 1 mysql> END; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END' at line 1 (Had you posted the actual error code, it would have been much nicer). So, yeah, you need to set the delimiter. -Sheeri On 5/18/06, Frederic Belleudy <[EMAIL PROTECTED]> wrote: Hi all! server version: 5.0.18 I attempted to create a trigger with statement and update, it does like this: CREATE TRIGGER update_trans AFTER UPDATE ON main_trans FOR EACH ROW BEGIN IF OLD.trans_status='INITIAL' and OLD.trans_state='INITIAL' THEN update trans set trans_status=NEW.trans_status, trans_state=NEW.trans_state where main_trans_id=NEW.main_trans_id; END IF; END; I 'm getting an error after : update trans set ... I tried to insert BEGIN update trans set .. END Note that I'm creating the trigger directly in phpmyadmin, I already created succesfully a trigger but it was really a simple one... I did try the delimiter "$$" or any other delimiter of my choice, but it doesn't accept the DELIMITER declaration before my create trigger Nothing works Any tips!? Is that possible to do this kind of trigger!? Tks for your answers, I appreciate!! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to default column value to lower( )
Not that I know of, but if you don't do binary (case-sensitive) searching then does it really matter?? You can retrieve with LOWER, or put it in your application, if your application needs to display it that way. -Sheeri On 5/18/06, Ferindo Middleton <[EMAIL PROTECTED]> wrote: I have column and I want to make sure the db is always making sure the value that gets input into this VARCHAR() column is always lowercase; Is there a way to set the value of a column within a table to automatically be lowercase. I know how to use the LOWER() function when performing queries but is there a way to define LOWER( ) within the definition of the table column itself without having the application specify LOWER( ) to any value passed to this column or if a record had to be manually input and the admin forgot to make sure all the characters were lowercase. Ferindo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Momentary huge replication lag
On 5/19/06, Martijn van den Burg <[EMAIL PROTECTED]> wrote: Nothing special, just some updates on a single database. No flushing of logs... The strange thing is that the condition of extremely high lag lasts only a couple of seconds, and then tapers back very quickly to zero. That is weird. If it only lasts a couple of seconds, how are you monitoring it to find out what the lag time is? -Sheeri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [5] Starting Up General Query Log
put log or log=/path/to/file in your config file (my.cnf) and restart the server. To turn it off you have to take it out of the my.cnf and restart the server. I've put in a request to make the general log something that can be dynamically turned on. -Sheeri On 5/19/06, Rich <[EMAIL PROTECTED]> wrote: Mac OS X 10.4.6 (Tiger), MySQL 5.0.21. Hi folks. I'm needing to start up my general query log to see what's ticking me off. I've looked into safe_mysqld but it's confusing as ... something that's confusing. Anybody know how I can easily turn this thing on for a day, then turn it off? I'm assuming put the following command has to be entered: --log = myqueries.log Cheers -- 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: Is This A Redundant Info Example?
On 5/19/06, Martijn Tonies <[EMAIL PROTECTED]> wrote: Well, one reason could be, for example, that the address changes over time and they want to know what address the customer was when the order was processed. I agree. I wouldn't enter the "delivery" and "billing" info either, but create some sort of "customer_address" table or "order_customer_address" in which records would be inserted if and only if the addresses used for billing and delivery differ from the normal customer address. And what happens if the customer changes their "normal" address but you want to keep that historical information? This is mostly an exercise in thinking what can happen, which you want to do. The schema originally presented is NOT normalized, but I don't see that it matters. An address for an order is just that, and isn't technically redundant data, because if the customer's address changes, the address for any *closed* orders won't change. I will agree that it's redundant because for the most part the addresses will be the same. So an "Addresses" table and a reference to the billing and shipping address ids in the orders table, and a reference to the "normal" address in the customer table is the way I'd go -- that way if the customer changes their address you don't have to change historical data (which is a bad idea anyway, and you'd need an audit trail if you did that). -Sheeri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: theoretical conn or thread limits
On 5/19/06, Lyle Tagawa <[EMAIL PROTECTED]> wrote: Given a nptl/linux box (or pthreads/freeBSD) for example, can you tell what is the theoretical max running thread count (in the context of paging/process scheduling and not in the context of memory sizing), assuming that there's no configuration-level cap on open files, etc. (i'd imagine we'd want to ignore cpu (and hence query complexity) and assume query caching is disabled, etc, for any theoretical model) I haven't come across this kind of tuning or capacity planning guide yet. Any pointers would be very helpful. That's because ignoring cpu and query complexity isn't generally done. Sure, you can run a zillion queries per second if all you're doing is "SELECT num from table;". But really threads are limited by memory usage, of which the total might be up to: innodb_buffer_pool_size + key_buffer_size + max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size) + max_connections*2MB (1 cxn = 1 thread. Remember that a replication master uses 1 thread per slave, and a replication slave uses 2 threads). The more memory a query uses, the fewer threads you can have. If your application is a reporting one, you want fewer threads with more memory allocated. If your application is a high-volume one, you want to keep your queries as light as possible. CPU speed and disk speed (I/O / seeking) definitely factor into things, because the faster they are, the faster queries will finish, and you'll have fewer concurrent threads. Also making sure that your logs and data use different seeks (ie, are on different disks) will help. Ignoring query complexity means that any number you come up with is pretty useless -- it doesn't usually matter what the performance is if you're not using representative queries. Unless this is theoretical research for a thesis. You really wouldn't want to tell your boss that "ignoring complexity, if we buy this machine we can have this performance!" -- your boss will forget the first part and then get upset that the machine doesn't have the performance you promised. -Sheeri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fwd: update problem with timestamp columns
Did you successfully alter the table? What does SHOW CREATE TABLE give you? mysql> CREATE TABLE test ( id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, ts_create TIMESTAMP DEFAULT CURRENT_TIMESTAMP, ts_modify TIMESTAMP ); Query OK, 0 rows affected (0.00 sec) mysql> insert into test(id) VALUES();insert into test(id) VALUES();insert into test(id) VALUES();insert into test(id) VALUES(); Query OK, 1 row affected, 1 warning (0.00 sec) Query OK, 1 row affected, 1 warning (0.00 sec) Query OK, 1 row affected, 1 warning (0.00 sec) Query OK, 1 row affected, 1 warning (0.00 sec) (the warnings are that the '' for id was truncated and the auto_increment was used) mysql> select * from test; ++-+-+ | id | ts_create | ts_modify | ++-+-+ | 1 | 2006-05-19 11:18:07 | -00-00 00:00:00 | | 2 | 2006-05-19 11:18:24 | -00-00 00:00:00 | | 3 | 2006-05-19 11:18:25 | -00-00 00:00:00 | | 4 | 2006-05-19 11:18:25 | -00-00 00:00:00 | ++-+-+ 4 rows in set (0.00 sec) and then I update to see if it changes the timestamp at ts_create: mysql> update test set ts_modify=DATE_SUB(NOW(),INTERVAL 6 MONTH); Query OK, 4 rows affected (0.00 sec) Rows matched: 4 Changed: 4 Warnings: 0 mysql> select * from test; ++-+-+ | id | ts_create | ts_modify | ++-+-+ | 1 | 2006-05-19 11:18:07 | 2005-11-19 11:18:59 | | 2 | 2006-05-19 11:18:24 | 2005-11-19 11:18:59 | | 3 | 2006-05-19 11:18:25 | 2005-11-19 11:18:59 | | 4 | 2006-05-19 11:18:25 | 2005-11-19 11:18:59 | ++-+-+ 4 rows in set (0.00 sec) So you see, it worked -- it did not update the ts_create timestamp. I would guess your alter table failed. -Sheeri On 5/18/06, Danish <[EMAIL PROTECTED]> wrote: Hi, I have a table with a time stamp column defined as ts timestamp(14) NOT NULL this is the first timestamp value in a series of timestamp columns. Whenever I update a row in the table ts gets updated with the current timestamp. I read from the documentaion that the first timestamp column in a create statement with neither DEFAULT nor ON UPDATE clauses is the same as DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP. To solve the problem I try to alter the table with the following command: ALTER TABLE `test` CHANGE `ts` `ts` TIMESTAMP( 14 ) NOT NULL DEFAULT 'CURRENT_TIMESTAMP()' but whenever I update a row after running the command above ts still gets updated. Any ideas how I can restrict mysql to not update the ts value on update. BTW, Iam testing this on 3.23 but it has the same effect on 4.1 as well Regards, Danish -- 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: Momentary huge replication lag
I've found that queries that take a long time cause lag time. Replication on a slave has 2 threads -- one to retrieve stuff from the logs, and another to actually run the DML queries. Therefore, while one thread is stuck on a loggg query, the other thread is still gathering stuff from the master, and that causes lag times. The value you gave for seconds_behind_master is about 58 hours -- that seems unusually high. What are you doing to flush logs, etc? -Sheero On 5/19/06, Martijn van den Burg <[EMAIL PROTECTED]> wrote: Hi, Recently I created a new replication set up with 5.0.18-standard-log on Solaris 8 (one master, one slave). Replication is running, but periodically (after a bunch of INSERT/UPDATE statements) there is a very large replication lag, with Seconds_Behind_Master values of 21 and more. This situation lasts for a few seconds and then all is normal. What could be the cause? I never had this happen with 4.1.10. Apologies for cross-posting; the volume on the 'replication' list is so low that I feared there might be no answer ;) Regards, Martijn -- 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: Handler and flush tables with read lock
Well, it's documented here: http://dev.mysql.com/doc/internals/en/flush-tables.html "Every time a thread releases a table, it checks if the refresh version of the table (updated at open) is the same as the current refresh_version. If not, it will close it and broadcast a signal on COND_refresh (to await any thread that is waiting for all instances of a table to be closed)." -Sheeri On 5/17/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: Hi, If you "open" a table using, HANDLER tablename OPEN; and then (in another MySQL command windows), execute, flush tables with read lock; The flush tables "hangs" until you execute a HANDLER tablename CLOSE; command. Is this a bug? Can anyone explain this? Many Regards, Ian Collins. -- 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: Baffled by query error syntax
Mike,
I can't really help except to ask if you're sure you copied and pasted
the query correctly. I did a similar query against a test system:
select u.uid,u.username,b.buddyUid,SEC_TO_TIME(SUM(TIME_TO_SEC(u.modified)))
as mins from Users u left join BuddyList b on u.uid = b.uid where
u.modified >= DATE_SUB(NOW(),INTERVAL 14 DAY) and country="au" group
by u.uid having mins >= '2' order by mins;
Similar joins, similar where clause, etc and yet I got an answer
(almost 700 rows, took 2 seconds) while you got a syntax error.
select @@version;
+-+
| @@VERSION |
+-+
| 4.1.12-standard-log |
+-+
So I'm not sure what to recommend other than trying the query again to
make sure there aren't typos.
MySQL usually gives a syntax error *where* the error happens. In this
case, it would indicate a problem with "SEC_TO_TIME(" but there
shouldn't be a problem, both according to the manual AND according to
my example.
I would prepare for a bug report -- create 2 new tables in the test
db, in this case you don't need a lot of test data, do the join, and
if you still get the problem, submit a bug report (you've just done
the "steps to recreate" part). Many times I've done this and realized
where my bug was because the query worked in the test table.
-Sheeri
On 5/17/06, Mike Blezien <[EMAIL PROTECTED]> wrote:
Hello,
this is a continued problem we are having from a earlier posting to the list
regarding a query. We need to calculate the SUM of the column
'agent_product_time' which is a TIME datatype column and according to the
manual:
http://dev.mysql.com/doc/refman/4.1/en/date-and-time-type-overview.html
this is the way to SUM the total time, which keeps producing a syntax error and
figure out why
MySQL version 4.1.12
---
SELECT c.account_id,a.name,a.company,
SEC_TO_TIME(SUM(TIME_TO_SEC(c.agent_product_time))) AS mins
FROM account a LEFT JOIN calls c ON c.account_id = a.id
WHERE c.calldate >= DATE_SUB(NOW(),INTERVAL 14 DAY)
AND c.agent_id = 2 GROUP BY c.account_id HAVING mins >= '500' ORDER BY mins
ERROR:
#1064 - You have an error in your SQL syntax; check the manual that corresponds
to
your MySQL server version for the right syntax to use near
'( SUM( TIME_TO_SEC( c . agent_product_time ) ) ) AS mins FROM account a LEFT
JO' at line 1
--
What would be producing the syntax error here.??
Again, any help would be much appreciated.
Mike(mickalo)Blezien
===
Thunder Rain Internet Publishing
Providing Internet Solution that Work
http://www.thunder-rain.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: about log file
The short answer is "in the config file." The long answer is to read: http://dev.mysql.com/doc/refman/4.1/en/log-files.html Or whatever manual version is appropriate to your MySQL version. -Sheeri On 5/17/06, Peng Yi-fan <[EMAIL PROTECTED]> wrote: Hi everyone, How to change the log file path of a database. I am using Window XP OS. Thanks. Peng -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem running mysql_install_db
Check out line 1 of the fill_help_tables.sql file. (on my installation it was in /usr/share/mysql) See if there is an errant ". There shouldn't be, of course, but that's the problem according to MySQL. -Sheeri On 5/15/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: I start from an up-to-date fedora core 4 (except that I've not rebooted lately, so I have newer kernels than I'm now running). This includes mysql-4.1.16-1.FC4.1.i386.rpm mysql-devel-4.1.16-1.FC4.1.i386.rpm mysql-server-4.1.16-1.FC4.1.i386.rpm mysqlclient10-3.23.58-6.i386.rpm I'm trying to follow directions in http://dev.mysql.com/doc/refman/4.1/en/unix-post-installation.html Transcript: # su mysql bash-3.00$ cd /var/lib bash-3.00$ mysql_install_db Installing all prepared tables Fill help tables ERROR: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 060514 21:39:56 [ERROR] Aborting 060514 21:39:56 [Note] /usr/libexec/mysqld: Shutdown complete WARNING: HELP FILES ARE NOT COMPLETELY INSTALLED! The "HELP" command might not work properly To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: /usr/bin/mysqladmin -u root password 'new-password' /usr/bin/mysqladmin -u root -h don-eve.dyndns.org password 'new-password' See the manual for more instructions. You can start the MySQL daemon with: cd /usr ; /usr/bin/mysqld_safe & You can test the MySQL daemon with the benchmarks in the 'sql-bench' directory: cd sql-bench ; perl run-all-tests Please report any problems with the /usr/bin/mysqlbug script! The latest information about MySQL is available on the web at http://www.mysql.com Support MySQL by buying support/licenses at https://order.mysql.com bash-3.00$ What's the error in syntax? I'm now simply following the directions above that say to use mysqlbug and tell you what I did. I can't tell for sure,but it looks like I'm supposed to just mail this buffer. That's another problem. I seem to be in emacs (not the right one) and not in a mail buffer ... I'll try to mail it anyway... >Description: >How-To-Repeat: >Fix: >Submitter-Id: >Originator:root >Organization: >MySQL support: [none | licence | email support | extended email support ] >Synopsis: >Severity: <[ non-critical | serious | critical ] (one line)> >Priority: <[ low | medium | high ] (one line)> >Category: mysql >Class: <[ sw-bug | doc-bug | change-request | support ] (one line)> >Release: mysql-4.1.16 (Source distribution) >C compiler:i386-redhat-linux-gcc (GCC) 4.0.2 20051125 (Red Hat 4.0.2-8) >C++ compiler: i386-redhat-linux-g++ (GCC) 4.0.2 20051125 (Red Hat 4.0.2-8) >Environment: System: Linux don-eve.dyndns.org 2.6.15-1.1831_FC4 #1 Tue Feb 7 13:37:42 EST 2006 i686 i686 i386 GNU/Linux Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Using built-in specs. Target: i386-redhat-linux Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --enable-checking=release --with-system-zlib --enable-__cxa_atexit --disable-libunwind-exceptions --enable-libgcj-multifile --enable-languages=c,c++,objc,java,f95,ada --enable-java-awt=gtk --with-java-home=/usr/lib/jvm/java-1.4.2-gcj-1.4.2.0/jre --host=i386-redhat-linux Thread model: posix gcc version 4.0.2 20051125 (Red Hat 4.0.2-8) Compilation info: CC='i386-redhat-linux-gcc' CFLAGS='-O2 -g -pipe -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -m32 -march=i386 -mtune=pentium4 -fasynchronous-unwind-tables -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE -fno-strict-aliasing' CXX='i386-redhat-linux-g++' CXXFLAGS='-O2 -g -pipe -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -m32 -march=i386 -mtune=pentium4 -fasynchronous-unwind-tables -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE -fno-strict-aliasing -fno-rtti -fno-exceptions' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx 1 root root 13 Mar 28 06:49 /lib/libc.so.6 -> libc-2.3.6.so -rwxr-xr-x 1 root root 1481808 Mar 14 05:31 /lib/libc-2.3.6.so -rw-r--r-- 1 root root 2519920 Mar 14 05:00 /usr/lib/libc.a -rw-r--r-- 1 root root 238 Mar 14 04:26 /usr/lib/libc.so lrwxrwxrwx 1 root root 10 Nov 19 07:11 /usr/lib/libc-client.a -> c-client.a lrwxrwxrwx 1 root root 20 Nov 19 07:11 /usr/lib/libc-client.so -> libc-client.so.2004g -rwxr-xr-x 1 root root 800272 Dec 1 11:58 /usr/lib/libc-client.so.0 -rwxr-xr-x 1 root root 828604 Nov 17 05:25 /usr/lib/libc-client.so.2004g Configure command: ./configure '--build=i386-redhat-linux' '--host=i386-redhat-linux' '--target=i386-redhat-linux-gnu' '--program-prefix=' '--prefix=/usr' '--exec-prefix=/usr' '--bindir=/usr/bin' '--s
Re: need help in updating old binary files to database slave
I've had this same problem -- if the database loses connection at the same time the log file flushes, you need to SET MASTER_LOG_FILE and MASTER_LOG_POS again and restart. I believe this probably qualifies as a bug if you want to report it. The reason the slave isn't updating log5 is because it's at the end of the logfile -- check out the position, and then check out the length of the file. -Sheeri On 5/15/06, balaraju mandala <[EMAIL PROTECTED]> wrote: Hi Comunity, I need some help from you. In replication i want to update old binary files, but server is not updating old files. It is updatin only current using binarylog only. The scenario is, my master is updating log-5 file(say) and slave is reading data from it, and updating itself to current changes. but connection is lost between master and slave. After the connection is established the new log-6 file is update by master but at slave side its only log-5. slave is not updating log-5 though connection is ok. what i have to do so that the old binary file have to be updated in slave. regards, bala -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with prcedures
You cannot get data out of a database if it's not in the database. If there's no data for a day, you cannot get 0 for that day, because the day does not exist in the database. You could make a "calendar" table, one row per day, and join it with a count to get 0 for the day. -Sheeri On 5/15/06, Barry <[EMAIL PROTECTED]> wrote: Hello everyone! The mysql documentation doesn't show any good infos er examples about writing procedures. To be more specific: I have a table with saved clicks by users. Now i want to draw a graph with a php extension. Problem is: if i let me show the clicks, one day is missing. Because on that day noone clicked on the link. I use this query: SELECT DATE(c_clicktime) AS clicktime, count(c_id) as clicks FROM clicks WHERE c_cl_id = 3 AND DATE(c_clicktime) BETWEEN '2005-01-01' AND '2005-01-20' GROUP BY clicktime The output gives me 19 entries because on the 20th January noone clicked that link. I think this have to be done with a procedure. So that my query would give a 0 (zero) for the 20th January as clicks. How would i do something like that? Thanks for your time :) Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- 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: Unknown option --install
Yes -- did it help? -Sheeri On 5/15/06, Miles Thompson <[EMAIL PROTECTED]> wrote: Sheeri, So even if I am executing mysqld-nt.exe from the directory where it is installed, preface it with the full path? Never thought of that - worth a try. Thanks - Miles At 05:23 PM 5/12/2006, sheeri kritzer wrote: >I'm going to guess that the path variable is only looking at the MySQL >3.23 mysqld binary. Try using a full path to the MySQL 5.0.20 binary >and see if you still get errors. > >-Sheeri > >On 5/10/06, Miles Thompson <[EMAIL PROTECTED]> wrote: >> >>I am trying to install two MySQL servers to run as Windows XP services. >>One for work with php-gtk+ as mysqld1, MySQL 3.23.55 on port 3306 >>and another as mysqld2, MySQL 5.0.20 on port 3308 for PHP5 development and >>testing. >> >>I'm following the manual's instructions in section 5.13.1.2. Starting >>Multiple Windows Servers as Services >>found at >>http://dev.mysql.com/doc/refman/5.0/en/multiple-windows-services.html >> >>The installation of mysqd1 went fine, but I consistently get an error when >>trying to install mysqld2, like so: >> >>C:\PROGRA~1\xampp\mysql\bin>mysqld-nt --install mysqld2 >>--defaults-file=C:/Program Files/xampp/mysql/bin/my_opts5.cnf >> >>Which returns this error: >>060510 12:18:34 [ERROR] mysqld-nt: unknown option '--install' >> >>Huh? It's listed as one of the parameters after issuing mysqld-nt --help >>--verbose. >> >>More background: >> The previous instances of MySQL services have been removed. >> Have tried both forward "/" and back "\" slashes in the >> defaults-file path >> Console window has been closed and reopened. >> mysql.ini in the \Windows directory has been renamed to >> mysql.ini.old >> >>Does anyone have any suggestions? They will be most welcome. >> >>Regards - Miles Thompson >> >> >>-- >>No virus found in this outgoing message. >>Checked by AVG Anti-Virus. >>Version: 7.1.392 / Virus Database: 268.5.5/335 - Release Date: 5/9/2006 >> >> >> >>-- >>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] > > -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.1.392 / Virus Database: 268.5.6/339 - Release Date: 5/14/2006 -- 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: Setting sql_log_bin
I don't know what version you're running, but a
mysql -?
will show you the variables you can set. It's not ALL variables you
can set that way, just a few. You will see that the variable you're
trying to set cannot be set via a command on the commandline.
However, your first command could be to SET SQL_LOG_BIN=0 in your
script or whatever you're running.
For 5.0.19, here is the snippet of mysql -? that shows the variables
you can set:
Variables (--variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
- -
auto-rehash TRUE
character-sets-dir(No default value)
default-character-set latin1
compress FALSE
database (No default value)
delimiter ;
vertical FALSE
force FALSE
named-commandsFALSE
local-infile FALSE
no-beep FALSE
host (No default value)
html FALSE
xml FALSE
line-numbers TRUE
unbufferedFALSE
column-names TRUE
sigint-ignore FALSE
port 0
promptmysql>
quick FALSE
raw FALSE
reconnect TRUE
socket(No default value)
table FALSE
debug-infoFALSE
user (No default value)
safe-updates FALSE
i-am-a-dummy FALSE
connect_timeout 0
max_allowed_packet16777216
net_buffer_length 16384
select_limit 1000
max_join_size 100
secure-auth FALSE
show-warnings TRUE
-Sheeri
On 5/15/06, Mattias Andersson <[EMAIL PROTECTED]> wrote:
Hi.
I can't figure out how to set the session variable SQL_LOG_BIN when
using the mysql command-line client.
I want to do something like: #mysql -u root -pmypasswd --sql_log_bin = 0
< somefile.sql.
When trying to run this mysql tells me:
/usr/bin/mysql: unknown option '--sql_log_bin'
I figure from the documentation that this should be possible:
mysql --help
---snip---
-O, --set-variable=name
Change the value of a variable. Please note that this
option is deprecated; you can set variables
directly with
--variable-name=value.
---snip---
Perhapps the problem comes from that this is a sessions-variable...?
The problem I would like to solve is to not bin-log some special data
imports.
Any one that could help me figure this out?
Thanks alot.
Regards,
/Mattias
--
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: "optemizer" for mySQL!
I'm not sure what you mean by profiler -- where are you getting your information from? It's not referenced in the article I sent. If you mean checking to see if indexes are being used during a query, check out the EXPLAIN syntax. http://dev.mysql.com/doc/refman/5.0/en/explain.html -Sheeri On 5/14/06, Jim <[EMAIL PROTECTED]> wrote: Thanks Sheeri, How do you run the profiler and what does it do? Have recently had to add some indexes to various tables to improve performance so does not seen to be running automatically. -Original Message----- From: sheeri kritzer [mailto:[EMAIL PROTECTED] Sent: Saturday, 13 May 2006 2:05 AM To: Jim Cc: mysql@lists.mysql.com Subject: Re: "optemizer" for mySQL! http://www.devshed.com/c/a/MySQL/A-Technical-Tour-of-MySQL/ The MySQL server has it built in. -Sheeri On 5/8/06, Jim <[EMAIL PROTECTED]> wrote: > Hi All, > > > > Didn't know there was an "optemizer" for mySQL. > > Where can I get it from? > > > > Thanks > > Jim > > > > > > Best regards, > > > > Jim Clark > Project Manager > Multilink Systems > Ph: 03 9425 9400 > Fax: 03 9425 9811 > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL crashes randomly
So, our MySQL master database crashes about once a week, then immediately recovers. We are running a Dell 2850 -- 64-bit Fedora Core 3 box with 6G of memory, 4 Intel Xeon processors, at 3.60 GHz speed each (says /proc/cpuinfo), each cpu cache size is 2048 Kb. It replicates to 2 slaves, which have the same hardware and memory. (the slaves don't crash). I've done everything at http://dev.mysql.com/doc/refman/4.1/en/crashing.html uname -a Linux dbhotsl1.manhunt.net 2.6.12-1.1381_FC3smp #1 SMP Fri Oct 21 04:22:48 EDT 2005 x86_64 x86_64 x86_64 GNU/Linux cat /proc/meminfo MemTotal: 6142460 kB MemFree: 26564 kB Buffers: 15396 kB Cached: 805128 kB SwapCached: 1336 kB Active: 5503352 kB Inactive: 505792 kB HighTotal: 0 kB HighFree: 0 kB LowTotal: 6142460 kB LowFree: 26564 kB SwapTotal: 2096472 kB SwapFree: 2088036 kB Dirty: 1996 kB Writeback: 0 kB Mapped: 5195364 kB Slab: 78348 kB CommitLimit: 5167700 kB Committed_AS: 5532772 kB PageTables: 12384 kB VmallocTotal: 34359738367 kB VmallocUsed: 263636 kB VmallocChunk: 34359474295 kB HugePages_Total: 0 HugePages_Free: 0 Hugepagesize: 2048 kB The server regularly runs at 20-30 MB free memory all the time, so it's not (necessarily) a low memory issue. We get the dreaded "Signal 11" error, and no core dumps even though we have core-file set in the [mysqld] of the my.cnf. Speaking of the my.cnf, here it is: --- [mysqld] core-file old-passwords tmpdir = /tmp/ datadir = /var/lib/mysql socket = /var/lib/mysql/mysql.sock port = 3306 key_buffer = 320M max_allowed_packet = 16M table_cache = 10240 thread_cache = 80 ft_min_word_len = 3 # Query Cache Settings - OFF due to overload of Session table query_cache_size = 32M query_cache_type = 2 # Log queries taking longer than "long_query_time" seconds long_query_time = 4 log-slow-queries = /var/log/mysql/slow-queries.log log-error = /var/log/mysql/mysqld.err # Try number of CPU's*2 for thread_concurrency thread_concurrency = 12 interactive_timeout = 28800 wait_timeout = 30 # up to 15 Apache Servers with 256 connections each = 3840 # 5.8 G of memory = 2200 cxns # when you change this recalculate total possible mysqld memory usage!! # innodb_buffer_pool_size + key_buffer_size # + max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size) # + max_connections*2MB max_connections = 2200 max_connect_errors = 128 # Replication Master Server (default) # binary logging is required for replication log-bin=/var/log/mysql/dbhotsl1-bin server-id = 18 binlog-do-db = db1 binlog-do-db = db2 binlog-do-db = db3 max_binlog_size = 2G # InnoDB tables innodb_data_home_dir = /var/lib/mysql/ innodb_data_file_path = ibdata1:3G;ibdata2:3G;ibdata3:3G;ibdata4:3G; innodb_log_group_home_dir = /var/log/mysql/ innodb_log_files_in_group = 2 innodb_log_arch_dir = /var/log/mysql/ innodb_buffer_pool_size = 4G innodb_additional_mem_pool_size = 40M innodb_log_file_size = 160M innodb_log_buffer_size = 80M innodb_flush_log_at_trx_commit = 0 innodb_lock_wait_timeout = 50 innodb_thread_concurrency = 8 innodb_file_io_threads = 4 ## [mysql.server] user=mysql basedir=/var/lib ## [safe_mysqld] err-log=/var/log/mysql/mysqld.log pid-file=/var/run/mysqld/mysqld.pid --- And then the error file, pretty standard, not really telling me anything (and no stack trace): -- mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=335544320 read_buffer_size=131072 max_used_connections=2201 max_connections=2200 threads_connected=152 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 5114862 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. 060427 23:56:44 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 060427 23:56:44 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 752 3907332354. InnoDB: Doing recovery: scanned up to log sequence number 752 3912574976 InnoDB: Doing recovery: scanned up to log sequence number 752 3917817856 [...more of the same] InnoDB: Doing recovery: scanned up to log sequence number 752 4144467558 060427 23:57:09 InnoDB: Starting an apply batch of log records t
Re: Questions about InnoDB, innodb_buffer_pool_size and friends
Hi Dan, You never really say what your memory problems are. Is MySQL crashing because you're trying to use too much memory? more comments inline On 5/11/06, Dan Trainor <[EMAIL PROTECTED]> wrote: What I'm dealing with here is memory problems using MySQL 5.0.19 under FreeBSD. Although I've enabled allocation of more memory per-process, as described by the FreeBSD notes for 5.0.x, I'm still seeing problems. [snip] We're trying to make our database 'hot', and stick as much of it as we can, into memory. I see a few problems with the current configuration (hopefully others can see more problems than I do): I don't have a innodb_log_file_size in there. I think that I would benefit from using this one, because the default is 5M. I believe that our bottleneck has a lot to do with disk I/O as well, so I think bringing this up substantially would help. Are your logs and data on a different partition? If not, put them on different partitions, preferably different disks. If we have four ibdataN files of 500M each, there's no way that we can make this database 'hot', especially when taking into consideration that this is a 32bit platform. On top of that, our innodb_buffer_pool_size is set to 1600M. ibdata files set up a tablespace allocation for innodb tables. This is the max they can get to, and MySQL reserves the disk space so nobody else can touch it. But what goes into memory is actual data and indexes. So memory isn't going to take 2,000M -- unless your tablespace is totally full. At least that's my understanding of it. Seems a waste to allocate memory for data that don't exist! Another idea would involve dumping a 32bit platform in favor of a 64bit platform, and just throw more memory at it. But who's employer would be fond of that? ;) Mine for one! You want your database hot, and in memory, ...but why? For performance? Performance for your paying customers? Who are currently complaining? Then buying hardware that can have more memory is a good investment. Basically, the question is, "do you really need your database in memory or not?" If so, your employer should be willing to spend the money. If not, your employer should be willing to deal with the fact that the db is not in memory, and any resultant slow performance. A rhetorical question to think about: How do you know your entire database isn't in memory? -Sheeri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unknown option --install
I'm going to guess that the path variable is only looking at the MySQL 3.23 mysqld binary. Try using a full path to the MySQL 5.0.20 binary and see if you still get errors. -Sheeri On 5/10/06, Miles Thompson <[EMAIL PROTECTED]> wrote: I am trying to install two MySQL servers to run as Windows XP services. One for work with php-gtk+ as mysqld1, MySQL 3.23.55 on port 3306 and another as mysqld2, MySQL 5.0.20 on port 3308 for PHP5 development and testing. I'm following the manual's instructions in section 5.13.1.2. Starting Multiple Windows Servers as Services found at http://dev.mysql.com/doc/refman/5.0/en/multiple-windows-services.html The installation of mysqd1 went fine, but I consistently get an error when trying to install mysqld2, like so: C:\PROGRA~1\xampp\mysql\bin>mysqld-nt --install mysqld2 --defaults-file=C:/Program Files/xampp/mysql/bin/my_opts5.cnf Which returns this error: 060510 12:18:34 [ERROR] mysqld-nt: unknown option '--install' Huh? It's listed as one of the parameters after issuing mysqld-nt --help --verbose. More background: The previous instances of MySQL services have been removed. Have tried both forward "/" and back "\" slashes in the defaults-file path Console window has been closed and reopened. mysql.ini in the \Windows directory has been renamed to mysql.ini.old Does anyone have any suggestions? They will be most welcome. Regards - Miles Thompson -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.1.392 / Virus Database: 268.5.5/335 - Release Date: 5/9/2006 -- 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: #1191 - Can't find FULLTEXT index matching the column list
See response below:
On 5/9/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
It looks like today is my day! :)
I FULLTEXT indexed my table "products":
CREATE TABLE `products` (
`prod_id` int(8) unsigned NOT NULL auto_increment,
`prod_no` varchar(50) NOT NULL default '',
`prod_name` varchar(255) NOT NULL default '',
`prod_description` text,
`prod_colors` text,
`prod_includes` text,
`prod_catalog` varchar(45) default NULL,
`prod_status` enum('hidden','live','new') NOT NULL default 'new',
`prod_supplier` varchar(45) default NULL,
`prod_start_date` date default '-00-00',
`prod_end_date` date default '-00-00',
`prod_featured` enum('0','1') default NULL,
`on_sale` enum('Yes','No') NOT NULL default 'No',
PRIMARY KEY (`prod_id`),
UNIQUE KEY `prod_no` (`prod_no`),
KEY `products_index1` (`prod_status`),
KEY `products_index2` (`prod_start_date`,`prod_end_date`),
KEY `on_sale` (`on_sale`),
FULLTEXT KEY `prod_name` (`prod_name`),
FULLTEXT KEY `prod_description` (`prod_description`)
) TYPE=MyISAM AUTO_INCREMENT=3367 ;
When I tried this:
SELECT * FROM products
WHERE match (prod_name) against ('+red +shirt');
I'll get some results.
But, when I tried this:
SELECT * FROM products
WHERE match (prod_name, prod_description) against ('+red +shirt');
I got this error message:
#1191 - Can't find FULLTEXT index matching the column list
What am I doing wrong?
You put 2 FULLTEXT indexes on different columns, and you're trying to
match against one multi-column index, which doesn't exist. Your table
creation allows:
SELECT * FROM products
WHERE match (prod_name) against ('+red +shirt');
SELECT * FROM products
WHERE match (prod_description) against ('+red +shirt');
or
SELECT * FROM products
WHERE match (prod_name) against ('+red +shirt')
OR match (prod_description) against ('+red +shirt');
If you want to allow the query you originally wrote, you should have
one multi-column FULLTEXT index, like so:
FULLTEXT KEY `keyname` (`prod_name`,`prod_description`)
But I'm guessing what you want is to change your query -- which allows
you to match on either the name OR the description.
-Sheeri
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 1' and '1' or '1
I diagree on point 1. If you warn your members that their password is insecure, and if you e-mail out passwords anyway, there's no reason not to have a secure password. Many people I know use an insecure password for many things, from silly required free registration sites (go ahead, break into my Washington Post account) to other services not important to them. But do I really care if someone ganks my wikipedia password? If you're a bank, sure, encrypt. Or if you have important data. But it's not a hard and fast rule. In fact, I'd venture to say "don't use hard and fast rules." THINK about your situation, and if it makes sense. Does using SSL make sense? Does using encryption make sense? -Sheeri On 5/10/06, Johan Lundqvist <[EMAIL PROTECTED]> wrote: Hi Dave, 1st: Never, never, never store passwords in plain text!! Just don't do it. Store a hash of the password (ie md5 or something else). 2nd: Never pass any input from the Internet directly into a query without first checking it for sql injection. Take a look at Wikipedia article for a brief explanation and several links to further info. http://en.wikipedia.org/wiki/SQL_injection /Johan Critters wrote: > Hi > A user was able to log into my site using: > 1' and '1' or '1 > in the username and password box. > > I ran the query > > SELECT * FROM members WHERE name = '1' and '1' or '1' AND password = '1' and '1' or '1' > > And it returned all rows. Can someone explain to me why this happens, and if the steps I took (replacing the ' with a blank space when the user submits the login form) is enough to prevent a similar "hack" > > Appreciate any feedback. > -- > Dave -- 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: sql-99
http://www.ncb.ernet.in/education/modules/dbms/SQL99/ -Sheeri On 5/10/06, Peng Yi-fan <[EMAIL PROTECTED]> wrote: hi, does anyone know where to download ISO-SQL-99? PDF is best. thanks Pang -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ~Replication errors~
MySQL's pretty good when it describes an error. (if the error message is vague you might be screwed, but MySQL is pretty good). Which means: 1) You didn't properly change the max_allowed_packet on the master. what does show variables like "max_allowed_packet"; show you? 2) The slave is actually running fine and you're looking at an old error. What does SHOW SLAVE STATUS; show you on the master? 3) Did you change max_allowed_packet on the slave? I think it would need to be changed on the slave as well. -Sheeri On 5/10/06, Mohammed Abdul Azeem <[EMAIL PROTECTED]> wrote: Hi, Iam getting the following error on my Mysql Slave server. This happened when my disk space got full and there was no space left on the device. I managed to free up some space and then ran mysql> STOP SLAVE mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000110', MASTER_LOG_POS=850202232; mysql> START SLAVE I could find that replication started but with the following error. Can anyone help me out in fixing the issue ? I tried increasing the max_allowed_packet on master server but with no luck. 060510 0:56:22 [Note] Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'mysql-bin.000110' at position 850202232 060510 0:56:22 [ERROR] Error reading packet from server: log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master ( server_errno=1236) 060510 0:56:22 [ERROR] Got fatal error 1236: 'log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master' from master when readingdata from binary log 060510 0:56:22 [Note] Slave I/O thread exiting, read up to log 'mysql- bin.000110', position 850202232 Thanks in advance, Abdul. This email has been Scanned for Viruses! www.newbreak.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5.0 strange table creation 'func'
Hi Amer,
Indeed, the 'func' table in the 'mysql' database has the same
structure that you found in your client database. in your upgrade,
did something go wrong and you did a mysqldump of the mysql database
and reimport? Are you sure you're in the right database? What does
describe mysql.func;
show you?
Does the following work?
describe clientdb.func;
(where 'clientdb' is the name of the client database it's in)
If the former and the latter give you a table description and no
errors, I'd say it's OK to delete the func table in the client db. Is
it in all client dbs or just that one? A mysqldump/import could have
been editing and done wrongbut I haven't done an in-place upgrade,
so I can't say for sure what the upgrade might or might not do and if
there are bugs or not.
-Sheeri
On 5/11/06, Amer Neely <[EMAIL PROTECTED]> wrote:
I've just noticed in one of my databases a table named 'func', which I'm
positive I never created.
It was empty and has 4 columns:
mysql> describe func;
+---+--+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+---+--+--+-+-+---+
| name | char(64) | NO | PRI | NULL| |
| ret | tinyint(1) | NO | | 0 | |
| dl| char(128)| NO | | NULL| |
| type | enum('function','aggregate') | NO | | NULL| |
+---+--+--+-+-+---+
4 rows in set (0.01 sec)
A search for 'func' in the 5.0 docs reveals there apparently is a system
table in the mysql database called 'func', but I can't find anything
about why it would be created in a client database.
It does appear in my mysql database as well, but no others. Anyone got
an idea where it's coming from? And can I delete it from the client
database?
I suspect this may have something to do with my recent upgrade to 5.0
but I don't see the purpose, especially as it only appears in one of my
client databases.
--
Amer Neely
Home of Spam Catcher
W: www.softouch.on.ca
E: [EMAIL PROTECTED]
Perl | MySQL | CGI programming for all data entry forms.
"We make web sites work!"
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL commercial licence
Not particularly. You're not buying an upgrade, you're buying a license for a new product. You can use MySQL all you want for free, you just can't repackage or embed it without a license. I don't see it as an upgrade issue really. You'll want to rewrite many queries, add new features, etc to get the full benefits of MySQL 5.0. You're making another product. "A bit rich" is what I'd call Oracle's licensing fees. Just remember, the money's paying developers to make the product better -Sheeri On 5/12/06, Adam Lipscombe <[EMAIL PROTECTED]> wrote: Checking. I don't think the customer bought the "network" version. Apparently its MySQL Pro Licence V4. It's a bit rich not to offer upgrades at a discount IMO. Adam -Original Message- From: David Logan [mailto:[EMAIL PROTECTED] Sent: 12 May 2006 11:52 To: Adam Lipscombe Cc: 'MySQL List' Subject: Re: MySQL commercial licence Adam Lipscombe wrote: >Folks, > > >We have a commercial product that uses mysql 4.0.24. We bought a >commercial licence for this version. We want to upgrade to 5.0.21. > >I spoke with the MySQL saleswoman this morning and she says they don't >do upgrades and want another entire licence fee for v5.x. > > >Does anyone else have experience of this? Upgrading commercial >licences? What's the story? > > >Thanks - Adam > > > > Hi Adam, That is a very interesting development. Is your license under the MySQL Network? I am considering using this for a number of new servers and would have to rethink my strategy if this is the case. I notice their Network FAQ has Q: Does MySQL Network include MySQL 5.0? A: Yes, MySQL Network includes all updates and upgrades including MySQL 5.0 It is also very interesting that the Network product automatically includes a GPL covered product rather than a commercial one by default. How many people actually check on that before purchase? Are they aware they have purchased a GPL product and are now obligated under that license to GPL their distributed products? Regards -- David Logan South Australia when in trouble, or in doubt run in circles, scream and shout -- 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: What is the best coding ethics related to mysql
The courses MySQL offers are excellent. Perhaps good courses to take would be the MySQL DBA or Developer Certification Tutorials, and then take the certification tests. http://www.mysql.com/training/ I've taken their courses and have only good things to say. -Sheeri On 5/12/06, Barry <[EMAIL PROTECTED]> wrote: abhishek jain schrieb: > Hi all, > I have been using mysql from last few years but for small projects only, > recently i have been on to some good projects, I want toknow what is the > best coding practices for mysql to kee it fast etc. > I mean in mine earlier post one friend told me that size upto 4 GB can be > achieved with Mysql. > I want to know: > 1)Which is better a long table in terms of nos. of columns or use join and > increase the columns. eg. in simple registration site we have 20 columns , > we should use it in same table or use it in two diff. tables. Depends on what you want to do with that table. If you have lotsa crossover questions and need to mix up stuff. use more tables. otherwise use less. > 2)To use indexes to the maximum or restrain its use. To maximum. Read also the optimize sections of dev.mysql.com. They help a lot. > 3)etc. Blah blah > Pl. point me to good advanced tutorial of mysql. > Also is there any certification of mysql, php etc. What kind of cert you are talking about? SSL certs or what? Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- 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: Quick Linux/MySQL performance questions.
I'll add "make sure logs and data are on separate partitions" so
you're not doing excessive seeking back and forth.
-Sheeri
On 5/12/06, Dan Buettner <[EMAIL PROTECTED]> wrote:
Hope it is useful.
I agree, you may want to look at adding another card and disks, for
speed and to segregate the various operations (temp, logging, data).
Splitting up your MYD and MYI files may help, though if you have enough
RAM to keep indexes in memory, maybe you don't need to do that.
With the sheer size of your data, I suggest you consider some form of
striping with your RAID, not just mirroring. I'm a big fan of RAID 10
personally but if your data is pretty static then RAID 5 gives you the
read speed at a lower cost. If you have a lot of write operations then
RAID 5 may not be such a good choice. You might be surprised how much
you will gain in read speed and therefore MySQL query speed if you go
from RAID 1 to say a 6-disk RAID 10 setup.
Depends on funds of course. For a good LSI card and 6 small fast
internal disks you're probably looking at $2K or so. Depending on what
you have now you could put MySQL logging on some inexpensive slower
disks and re-use existing disks in a new setup.
Good luck!
Dan
RV Tec wrote:
> Buettner,
>
> First of all, thanks a lot for your reply!
>
> This server has 4 disks to MySQL usage, in two pairs of RAID-1,
> connected to a single channel (ok, I realize now this means a
> bottleneck) LSI PCIe card.
>
> One RAID1 for MySQL logging and temp space, and the other pair for the
> database files (MYI/MYD). I was planning a couple of things:
>
> 1) Add another LSI card, this time, 2-channel. Put the MYI files on one
> mount point, and the MYD at the other one -- different channels.
>
> 2) Find a way to measure the max size of the tempdir, used by MySQL.
> Depending on its size, I could use a MFS partition. This could avoid me
> some "Copying to tmp table", I guess.
>
> What I'm scared to death, is that our queries are really complex, with
> lots of left joins and lots of large tables used. Some queries are now
> reaching 30 minutes to return... we do have slow queries active, and
> after I'm sure the hardware/OS is OK, we'll nail this and try to get it
> better.
>
> Best regards,
> RV
>
> On Fri, 12 May 2006, Dan Buettner wrote:
>
>> Good morning RV -
>>
>> On your 3rd question, about how to make things faster:
>>
>> More RAM should help by allowing the server to keep more/all of the
>> indexes in memory, enabling much faster access. Be sure to adjust the
>> cache settings in your my.cnf file after adding RAM. (Keep in mind -
>> some my.cnf memory settings are per database server instance and some
>> are per connection thread instance!) Large databases eat RAM for
>> breakfast. The rest of your hardware setup sounds really quite good.
>>
>> One possibility for some improvement might be to look at adding
>> dedicated fast disks for MySQL temp space, since you are dealing with
>> large datasets. 2 or more small fast disks in a striped setup,
>> especially on their own SCSI channel and ideally with their own
>> hardware RAID RAM cache, may reduce disk and I/O contention if your
>> temp space is currently on the same disks with your data. Of course
>> this will only be helpful if MySQL is actually using disk based temp
>> tables during large queries - check your status output to see.
>>
>> I've done a lot of reading on and experimentation with MySQL
>> performance and attended a MySQL training session on performance
>> tuning, and have learned: once you have reasonable hardware, the
>> biggest thing you can do to improve speed is to optimize your SQL
>> queries, indexes, and data structure. While improving your hardware
>> can give perhaps a factor of 10 performance increase, optimizing your
>> indexes and queries can sometimes give factors of 100's.
>>
>> Enable your slow query log, if you haven't already, and use the slow
>> query tool to start looking at what kinds of queries are taking "too
>> long" ("too long" being defined by you as a MySQL variable in number
>> of seconds). Start with the slow queries used most often and see how
>> you can optimize those, by adding or changing indexes for example.
>>
>> Read up on MyISAM performance, particularly when it comes to index
>> creation and usage. Keep in mind that 4.x and 5.x are slightly
>> different animals in this area (MyISAM index usage) and so read the
>> section for your version:
>> http://dev.mysql.com/doc/refman/4.1/en/optimization.html
>> Lots of indexes can be helpful, but MySQL may not be able to use them
>> well depending on how they were created: the order in which you
>> specify columns when creating a multi-column index affects how/whether
>> MySQL can use it for certain queries, for example.
>>
>> Hope this helps.
>>
>> Dan
>
--
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 arch
Re: ORDER BY making recordset non-updatable
Well, that clears things up on my end. I was confused as to how a SELECT statement produces "updatable results" because SELECT just gives output. Go into MySQL on the commandline and see if Select * from vwMyView and Select * from vwMyView ORDER BY Name produce the same results, just in a different order. If they do, as expected, then the problem is not in MySQL. Since you can update it in the application with the former select query and not the latter, I don't think it's permissions or anything; rather something in the application. It sounds like Visual Basic, as ODBC just passes connections along to MySQL. -Sheeri On 5/12/06, Eland, Travis M. <[EMAIL PROTECTED]> wrote: Hey Sheeri. Thanks again for the response. I do agree that this is a known problem with SQL Server. My problem is that I am using a Windows Visual Basic application to access a Linux based MySQL Server via MyODBC/Connector. There is no longer SQL Server in the loop. I believe this is a Microsoft Cursor Engine error that is being issued, and it just happens to be the same one that is associated with the SQL Server issue. I have been able to reproduce my problem on many levels. I created 2 basic tables, created a view where "table1 left join table2". If, in Visual Basic, I call the view and order by a table1 field, I can update. If I order by a table2 field, I can no longer update and get the "Invalid Key Column for Updating or Refreshing" error. While the issue stems from the view definition in MySQL, the actual problem could be caused by Visual Basic, MyODBC or MySQL itself. I don't know what to try next. As always, any help is greatly appreciated. Thanks again, Travis Eland -Original Message- From: sheeri kritzer [mailto:[EMAIL PROTECTED] Sent: Fri 5/12/2006 10:01 AM To: Eland, Travis M. Cc: mysql@lists.mysql.com Subject: Re: ORDER BY making recordset non-updatable That's a problem with SQL Server -- google search on your error and you'll see that that's associated with SQL server, not MySQL. -Sheeri On 5/11/06, Eland, Travis M. <[EMAIL PROTECTED]> wrote: > Thanks for the response! Unfortunately, it is that simple. > > A basic "Select * from vwMyView" yields an updatable recordset. > > Adding "Order by Name" to the end does not allow an update. > > I should mention that the error associated with the lack of update is: > "Insufficient Key Column Information for Updating or Refreshing" > > I have since futhered my troubleshooting and determined that I actually AM able to update the recordset when the order by is applied in some situations. Apparently, I can order by any field that is in the view's main table (the table that all of the other tables left join off of) and still be able to update. It is when I order by a field that is not from this main table that I get the above error and inability to update. > > I am still at a loss as to how to fix this so that I can order by any field I wish. > > Any input is greatly appreciated. > > Thanks, > Travis Eland > > > > > > From: sheeri kritzer [mailto:[EMAIL PROTECTED] > Sent: Thu 5/4/2006 4:15 PM > To: Eland, Travis M. > Cc: mysql@lists.mysql.com > Subject: Re: ORDER BY making recordset non-updatable > > > > Maybe I'm thick > > You have a view, called vwMyView. > > You SELECT rows from it, and you're able to update the view? > > Yet when you SELECT with an ORDER BY clause, you're not allowed to > update the view? > > I just do not understand how a read statement affects DML. I think > you're going to have to post the query you're using, as it's more > complex than a SELECT. Perhaps you're using a REPLACE INTO > SELECT statement? Or UPDATE WHERE IN (SELECT)? > > -Sheeri > > On 4/19/06, Eland, Travis M. <[EMAIL PROTECTED]> wrote: > > Heya. > > > > I am in the process of modifying a program to access data from a MySQL database instead of a SQL Server database. I have a view that is referenced as follows (through use of a data environment command): > > > > Select * from vwMyView where id = ? > > > > If I run this command, I get the data that I would expect, and I am able to update the data that I would expect to update (there are a few joins in the view so there are a couple fields that I understand that I cannot update). > > > > My problem is, if I add an ORDER BY statement at the end of this command, the recordset still returns data, but it becomes non-updatable. > > > > I would include my SQL, but unfortunately it is on a classified machine. I have verified the SQL numerous times and it works fine in every way except when
Re: Problem installing 5.0.21 on Suse 9E - it fails on me
It looks like MySQL installed but when it tried to start the server, the server crashed. Can you confirm that? If so, try starting it with the startup script -- you should get the same error. If it's still broken, start it with the core-file and core-file-size option (I recommend unlimited): http://mirrors.sunsite.dk/mysql/doc/refman/5.0/en/server-options.html and see if it dumps core, and try using that to figure out the problem. Other things: Is the intel system 32-bit or 64-bit? You're using a 32-bit package, which limits you on a 64-bit box at best but might cause the crash at worse. Yes, I know it runs fine on the 64 bit athlon, but I made that mistake once too, using a 32-bit binary on a 64-bit system. Worked fine until we wanted to use more than 4G of memory. I suggest installing the versions for: SuSE Linux Enterprise Server 9 RPM (AMD64 / Intel EM64T) on your AMD Athlon 64 and SuSE Linux Enterprise Server 9 RPM (Intel IA64) on your Intel, assuming it's 64 bit. Please let us know if that helped! -Sheeri On 5/7/06, John Correa <[EMAIL PROTECTED]> wrote: Hello, (linux novice: windows user migrating to linux) Hoping someone can point me in the right direction. Install of MySQL-server-standard-5.0.21-1.sles9.i586.rpm is failing with following error msg: "mysqld got signal 11". I have 2 Suse 9E systems and mysql installs fine on one but not the other. Both systems are new installs and have the same software installed (all rpms are same except kernel). kernel: 2.6.5-7.97-default --> installs okay here AMD Athlon64 kernel: 2.6.5-7.97-smp --> fails here Intel Dual Xeons MySQL-server-standard-5.0.19-0.sles9.i586.rpm installs perfectly on both systems. MySQL-server-standard-5.0.20a-0.sles9.i586.rpm also fails on the Intel Xeon system but installs fine on the AMD system. I think it has to do with my network configuration or the yaSSL features in 5.0.20+ I tried doing "resolve_stack_dump" but I am no linux programming expert. Any help would be appreciated. Details below JohnC full error msg --- MySQL-server-standard-5.0.21-1.sles9.i586.rpm mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=0 read_buffer_size=131072 max_used_connections=0 max_connections=100 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 217599 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=(nil) Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0xbffbcae4, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x8183f80 0xe420 New value of fp=(nil) failed sanity check, terminating stack trace! - - - Tried doing: resolve_stack_dump -s mysqld.sym -n mysqld.stack2 0x8183f80 handle_segfault + 656 0xe420 _end + -140375984 full error msg --- MySQL-server-standard-5.0.19-0.sles9.i586.rpm 060506 09:05:31 mysqld started mysqld: socket_wrapper.cpp:114: uint yaSSL::Socket::send(const byte*, unsigned int, int) const: Assertion `socket_ != INVALID_SOCKET' failed. mysqld got signal 6; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=0 read_buffer_size=131072 max_used_connections=0 max_connections=100 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 217599 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=(nil) Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0xbffbc9bc, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x8182090 0xe420 (nil) 0x400c9cd3 0x8401dd1 0x83f6e91 0x84017ad 0x83eb843 0x40209809 0x40209ad8 0x401caa9c 0x401cc671 0x401c382e Stack trace seems successful - bottom reached - Tried doing: resolve_stack_dump -s mysqld.sym -n mysqld.stack 0x8183f80 handle_seg
Re: "optemizer" for mySQL!
http://www.devshed.com/c/a/MySQL/A-Technical-Tour-of-MySQL/ The MySQL server has it built in. -Sheeri On 5/8/06, Jim <[EMAIL PROTECTED]> wrote: Hi All, Didn't know there was an "optemizer" for mySQL. Where can I get it from? Thanks Jim Best regards, Jim Clark Project Manager Multilink Systems Ph: 03 9425 9400 Fax: 03 9425 9811 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Memory Problem causing mysql to crash
Dobromir, As I said in my first message (see the message quoted below), MySQL could use up to 4.991913 G of memory. So you could use more than 4GB. Check out the calculation below. Also read the rest of my message, regarding thread size, the manual page for crashing, max_connections, slow query logs, and disk partitions. You haven't indicated that you've done any of what I mentioned, and you might be using more than 4G anyway. -Sheeri On 5/8/06, Dobromir Velev <[EMAIL PROTECTED]> wrote: Hi, I'm aware of the fact that this is a 32 bit system - and I've tried to make sure that mysqld will not use more than 4 GB. As you can see the innodb_buffer_pool_size is 2 Gb and the total amount of memory used by the MyISAM key buffer size and the per thread variables is less then 2 GB. There are no other services on this machine so the memory should not be a problem. This server was working fine for almost a year until recently it started crashing. Could it be some memory problem I've ran into and can you suggest anything I can do to avoid similar problems in the future. Thanks Dobromir Velev On Saturday 06 May 2006 01:23, Heikki Tuuri wrote: > Dobromir, > > you are running a 32-bit operating system. Then the size of the mysqld > process is limited to 2 GB, or at most to 4 GB. The amount of total RAM 8 > GB does not help here, since 2^32 = 4 G. > > You should reduce the key_buffer_size or innodb_buffer_pool_size in my.cnf. > > Best regards, > > Heikki > > Oracle Corp./Innobase Oy > InnoDB - transactions, row level locking, and foreign keys for MySQL > > InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM > tables > http://www.innodb.com/order.php > > - Original Message - > From: ""sheeri kritzer"" <[EMAIL PROTECTED]> > Newsgroups: mailing.database.myodbc > Sent: Friday, May 05, 2006 10:50 PM > Subject: Re: InnoDB Memory Problem causing mysql to crash > > > Well, according to my calculations: > > innodb_buffer_pool_size + key_buffer_size > > + max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size) > > + max_connections*2MB > > > > (I used the default binlog_cache_size value of 32K plus your settings) > > > > MySQL could use up to 4.991913 G of memory. Shouldn't be a problem, > > unless of course your 8G of machine is running something other than > > MySQL. Is it? Because the fact that it could not allocate memory > > means that something was trying to use memory that didn't exist > > > > Did MySQL dump a core file? > > > > Did you follow this advice? > > > >> You seem to be running 32-bit Linux and have 473 concurrent connections. > >> If you have not changed STACK_SIZE in LinuxThreads and built the binary > >> yourself, LinuxThreads is quite likely to steal a part of the global > >> heap= > > > > for > > > >> the thread stack. Please read http://www.mysql.com/doc/L/i/Linux.html > > > > Did you read the man page? > > > >> The manual page at http://www.mysql.com/doc/en/Crashing.html contains > >> information that should help you find out what is causing the crash. > > > > Also, did you try to look at your slow query logs to see if there was > > some kind of query hogging memory? What about backups running at the > > same time? > > > > I'll note that you maxxed out your connections, which shouldn't cause > > a crash, but might indicate that your server tuning is not up-to-date > > with your actual usage. > > > > Are your data and logfiles are on a diffferent partitions? We had > > problems with one machine where the data and logfiles were on the same > > partition, and it would crash -- we moved to a machine that was the > > same except for the different OS partitions, and it didn't crash! We > > figure the disk seeking just killed the OS so it segfaulted the mysql > > process. > > > > -Sheeri > > > > On 5/4/06, Dobromir Velev <[EMAIL PROTECTED]> wrote: > >> Hi, > >> I'm trying to resolve why InnoDB is crashing. It happened twice for the > >> l= > > > > ast > > > >> month without obvoius reason > >> > >> Any help will be appreciated. > >> > >> Dobromir Velev > >> > >> My Server is > >> Red Hat Enterprise Linux ES release 3 (Taroon Update 7) > >> 2.4.21-32.0.1.ELs= > > > > mp > > > >> Dual 3.2 GHz Intel Xeon > >> 8 GB RAM > >> with 3 x 146GB SCA Ultra 320 10K RPM SCSI Drives > >> > >> > >> my.c
Re: Optimizing range search with two-table ORDER BY
Hi Jesse, Have you tried the following: 1) ordering by only part.d and seeing how long the query takes 2) putting an index on (part.d, cwGroup.stripped_cw) and seeing how long the query takes. 1 will help pinpoint the problem, and 2 might actually help. -Sheeri SELECT part.d, quotation.qt, cwGroup.cw FROM cwGroup JOIN quotation ON (quotation.id = cwGroup.quotation_id ) JOIN part ON ( part.id = quotation.part_id ) WHERE ( part.d BETWEEN 1950 AND 1970 AND cwGroup.stripped_cw LIKE 'man%' ) ORDER BY part.d, cwGroup.stripped_cw LIMIT 25 and the EXPLAIN for it looks like: *** 1. row *** id: 1 select_type: SIMPLE table: cwGroup type: range possible_keys: quotation_id,stripped_cw key: stripped_cw key_len: 101 ref: NULL rows: 8489 Extra: Using where; Using temporary; Using filesort *** 2. row *** id: 1 select_type: SIMPLE table: quotation type: eq_ref possible_keys: PRIMARY,part_id key: PRIMARY key_len: 4 ref: rqs_incs.cwGroup.quotation_id rows: 1 Extra: *** 3. row *** id: 1 select_type: SIMPLE table: part type: eq_ref possible_keys: PRIMARY,d key: PRIMARY key_len: 4 ref: rqs_incs.quotation.part_id rows: 1 Extra: Using where Without the ORDER BY it drops to about 1.5s and EXPLAIN no longer shows the use of "temporary" and "filesort". An even worse example, but unfortunately a common need in this app, is a query that returns a lot of rows (but which I'm paging through, of course), such as: SELECT part.d, quotation.qt, cwGroup.cw FROM cwGroup JOIN quotation ON (quotation.id = cwGroup.quotation_id ) JOIN part ON ( part.id = quotation.part_id ) WHERE ( cwGroup.stripped_cw BETWEEN 'ant' AND 'asx' ) ORDER BY cwGroup.stripped_cw, part.d LIMIT 25 This takes 2m31s to execute, obviously due to the large number of rows (the total result is about 47K rows), but a similar query without the ORDER BY took only .08s (though a COUNT(*) took a similar 2-3m): *** 1. row *** id: 1 select_type: SIMPLE table: cwGroup type: range possible_keys: quotation_id,stripped_cw key: stripped_cw key_len: 101 ref: NULL rows: 54745 Extra: Using where; Using temporary; Using filesort *** 2. row *** id: 1 select_type: SIMPLE table: quotation type: eq_ref possible_keys: PRIMARY,part_id key: PRIMARY key_len: 4 ref: rqs_incs.cwGroup.quotation_id rows: 1 Extra: *** 3. row *** id: 1 select_type: SIMPLE table: part type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: rqs_incs.quotation.part_id rows: 1 Extra: Other queries, as said, are more complicated, adding additional columns in the searches or joining in other tables (sometimes with range searches here as well), but these don't seem to affect the underlying problem. Adding multiple-column indexes also doesn't affect things in any significant way. Any thoughts? I clearly need a significant speed improvement, not just a tweak like making a bigger sort_buffer_size or getting faster disks. Thanks for reading this far. Jesse Sheidlower -- 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: ORDER BY making recordset non-updatable
That's a problem with SQL Server -- google search on your error and you'll see that that's associated with SQL server, not MySQL. -Sheeri On 5/11/06, Eland, Travis M. <[EMAIL PROTECTED]> wrote: Thanks for the response! Unfortunately, it is that simple. A basic "Select * from vwMyView" yields an updatable recordset. Adding "Order by Name" to the end does not allow an update. I should mention that the error associated with the lack of update is: "Insufficient Key Column Information for Updating or Refreshing" I have since futhered my troubleshooting and determined that I actually AM able to update the recordset when the order by is applied in some situations. Apparently, I can order by any field that is in the view's main table (the table that all of the other tables left join off of) and still be able to update. It is when I order by a field that is not from this main table that I get the above error and inability to update. I am still at a loss as to how to fix this so that I can order by any field I wish. Any input is greatly appreciated. Thanks, Travis Eland From: sheeri kritzer [mailto:[EMAIL PROTECTED] Sent: Thu 5/4/2006 4:15 PM To: Eland, Travis M. Cc: mysql@lists.mysql.com Subject: Re: ORDER BY making recordset non-updatable Maybe I'm thick You have a view, called vwMyView. You SELECT rows from it, and you're able to update the view? Yet when you SELECT with an ORDER BY clause, you're not allowed to update the view? I just do not understand how a read statement affects DML. I think you're going to have to post the query you're using, as it's more complex than a SELECT. Perhaps you're using a REPLACE INTO SELECT statement? Or UPDATE WHERE IN (SELECT)? -Sheeri On 4/19/06, Eland, Travis M. <[EMAIL PROTECTED]> wrote: > Heya. > > I am in the process of modifying a program to access data from a MySQL database instead of a SQL Server database. I have a view that is referenced as follows (through use of a data environment command): > > Select * from vwMyView where id = ? > > If I run this command, I get the data that I would expect, and I am able to update the data that I would expect to update (there are a few joins in the view so there are a couple fields that I understand that I cannot update). > > My problem is, if I add an ORDER BY statement at the end of this command, the recordset still returns data, but it becomes non-updatable. > > I would include my SQL, but unfortunately it is on a classified machine. I have verified the SQL numerous times and it works fine in every way except when I use ORDER BY. The SQL structure (though slightly modified for mySQL) also worked fine in SQL Server. > > Is this a known issue? Is there something that I could possibly be missing? > > I apologize for the lack of actual code, but I appreciate any insight! > > Thanks! > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: VIEW not working with myODBC in XP and Access 2003
This is an Access problem; you'll need to find folks who know access to fix it. Try: http://www.tek-tips.com/viewthread.cfm?qid=1146857&page=1 ?? -Sheeri On 4/26/06, Daevid Vincent <[EMAIL PROTECTED]> wrote: I have a critical problem that I hope there is a simple solution for. I've just spent a couple days converting a very messy "hack" to populate a table using a much more elegant VIEW solution now. Everything is going great, except now the whole point of this VIEW is so that people using MS Access (or other ODBC) can use the VIEW. It doesn't work!?! I've followed all of this: http://dev.mysql.com/doc/refman/5.0/en/msaccess-setup.html I can import/link any other non-VIEW table. I've given FULL permissions to my ODBC user in mysql.mysql.user (and other appropriate) places just in case. Access pops up an error box that says "Could not execute query; could not find linked table" I'm using these versions: Windows XP http://dev.mysql.com/downloads/connector/odbc/3.51.html [EMAIL PROTECTED]:/lockdown# mysql --version mysql Ver 14.12 Distrib 5.0.15, for pc-linux-gnu (i686) using readline 4.3 -- 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: Consenus on best column type for Latitude / Longitude?
We use float. I have no idea if that's better or worse, but that's what we use. -Sheeri On 4/26/06, René Fournier <[EMAIL PROTECTED]> wrote: Just curious the majority use. I've been using decimal(18,14), but that appears bigger than necessary... Maybe varcar(21) for latitude, and varchar(22) for longitude? ...Rene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tuning a Server with >10,000 databases
Perhaps it's time to file a bug report, then? -Sheeri On 5/3/06, Alex <[EMAIL PROTECTED]> wrote: This problem is indeed not related to OS / Hardware Problems. Take a look at this thread: http://lists.mysql.com/mysql/197542 Read the part about show databases as root vs standard user + observed file system activity. -- 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: importing a dumpfile from with the mysql client
Ah, I get it.I don't know if there's a way to do that, but why not just put the SQL statements in the file? -Sheeri On 5/5/06, George Law <[EMAIL PROTECTED]> wrote: I think what he is saying is that be began the "transaction" in a command line client session from one location but was not able to give the > mysql> SET FOREIGN_KEY_CHECKS = 1; > mysql> COMMIT; commands in the same session. (ie - I owe, I owe, its off to work I go :) ) This is a good question, one I have wondered about myself. Is there a way in mysql to "attach" to session to issue a commit? -Original Message- From: sheeri kritzer [mailto:[EMAIL PROTECTED] Sent: Friday, May 05, 2006 3:02 PM To: Luke Vanderfluit Cc: MySQL List Subject: Re: importing a dumpfile from with the mysql client On 5/4/06, Luke Vanderfluit <[EMAIL PROTECTED]> wrote: [snip] > I started this process remotely then went to the site to finish it. > But when the dump finished (several hours later) I was not able to > execute the following commands from my original location. > > mysql> SET FOREIGN_KEY_CHECKS = 1; > mysql> COMMIT; What do you mean "you were not able"? Did you get an error? Was the server hung? Did the keyboard stick to make you unable to type the commands? > > My question is: > Since the import completed the database has grown in size and been > backed up etc. > Yet from the original session I have not executed those 2 commands. > > Is it safe to execute them? Or would executing them cause corruption or > other unforseen stuff? > Is it unnecessary to execute them? > Is it safe to kill that original mysql session? Those 2 commands, in and of themselves, aren't dangerous. It's what's in the script that could be the problem. However, since you really didn't mention what error you got, it's not easy to figure out how to fix it. It depends what tables you're running these on. If you're running them on MyISAM tables, deadlocking cannot happen. Then again, transactions are meaningless too. If you're running on InnoDB you have the possibility of deadlocking, but MySQL is pretty good about avoiding that. If other users/applications are writing to the SAME data then there's the possibility of inconsistent data, but I wouldn't expect corruption. if you post what errors you actually got, I might be able to help. Also, are you using the same db and tables that other apps/users are using? Does the dumpfile contain "DROP TABLE IF EXISTS" commands? -Sheeri > > -- > Luke Vanderfluit. > Analyst/Programmer. > Internode Systems Pty. Ltd. > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Searching a large table
well, you'd still have to use limit and offset with your search table would you store a different table for each unique query? That sounds like a lot of [temporary?] tables. are you doing ore than 3-4 table joins on ths one fulltext search query? If not, it's probably more work. If your queries are well-written and your indexes are good, there's no reason you'd need to make a search table unless you're doing some kind of data analysis with a warehouse. In my opinion, it's a waste of time, because paging queries with limit and offset will be fast enough. We do that on our site, and fulltext searching rarely shows up in our slow query logs. But this advice is worth exactly what you're paying for it, so there you have it. -Sheeri On 5/5/06, Steve <[EMAIL PROTECTED]> wrote: Hi Sheeri: Yes, you are misunderstanding my question. I certainly know how to limit my resultset to certain rows. I'm asking more about the effiencency of searching large volumes of data. Is making a search table like vBulletin does a good mechanism to avoid resource contention on the main table, or is that more work than what it's worth in my case? -- Steve - Web Applications Developer http://www.sdwebsystems.com On Fri, May 5, 2006 2:35 pm, sheeri kritzer said: > Sounds like you want LIMIT and OFFSET -- > > everything after my name and before your post is copied from the doc at > > http://dev.mysql.com/doc/refman/4.1/en/select.html > > (or am I misunderstanding your question?) > -Sheeri > > 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 both be non-negative integer constants (except when using > prepared statements). > > 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): > > SELECT * FROM tbl LIMIT 5,10; # Retrieve rows 6-15 > > To retrieve all rows from a certain offset up to the end of the result > set, you can use some large number for the second parameter. This > statement retrieves all rows from the 96th row to the last: > > SELECT * FROM tbl LIMIT 95,18446744073709551615; > > With one argument, the value specifies the number of rows to return > from the beginning of the result set: > > SELECT * FROM tbl LIMIT 5; # Retrieve first 5 rows > > In other words, LIMIT row_count is equivalent to LIMIT 0, row_count. > > For prepared statements, you can use placeholders (supported as of > MySQL version 5.0.7). The following statements will return one row > from the tbl table: > > SET @a=1; > PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?'; > EXECUTE STMT USING @a; > > The following statements will return the second to sixth row from the tbl > table: > > SET @skip=1; SET @numrows=5; > PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?, ?'; > EXECUTE STMT USING @skip, @numrows; > > For compatibility with PostgreSQL, MySQL also supports the LIMIT > row_count OFFSET offset syntax. > > On 5/5/06, Steve <[EMAIL PROTECTED]> wrote: >> All: >> >> I am developing a search engine and using MySQL as the backend database >> management system. Under normal circumstances, when users search >> through >> large volumes of records on a search engine site, the results are broken >> down into pages. When a user clicks on the 'Next' link, the system will >> re-query the database and return those records that pertain to that page >> (records 10 through 20, perhaps). This, as you can plainly see, >> requires >> the application to re-query the same data from the same table each time >> the user clicks on a 'Next' or 'Previous' link. I would imagine that >> this >> may lead to some resource contention on that main table, especially when >> many users are using the system simultaneously. >> >> I've never seen vBulletin's code, but I have been able to determine from >> careful analysis (and testing) that they employ a search table, of >> sorts, >> that contains the returned records from a search. So, when a user of >> the >> system submits a search query, the system returns the records and throws >> them into a separate search table, identified by a sequential primary >> key >> field. Then, the system uses that search table to display the >> appropriate >> records according to the respective search identifier (referenced in the >> URL), never touching the main table again until a brand new search is >> performed. >> >> This seems to be
Re: Coded fields
On 5/5/06, John Heim <[EMAIL PROTECTED]> wrote: In fact, I would not gain clarity by using the 5-char codes that have been imposed upon me. Freshman='10'. Sophomore='20'. There's even a '00' code and a '05' for some status less than Freshman. Imposed upon you? You're the DBA, right, not an end-user of this data? Data values are not "imposed upon you". Perhaps they're "introduced into an existing system" and "are not compatible". Honestly, I'd change the system to conform to theirs. Or add their codes to your code table and change it to varchar(5). Your system has been proven to be not flexible enough! I don't think you can get "them" to change their system, and it obviously works for "them". You might gain clarity, believe it or not. Folks who work with this data probably know that 10 is Freshman, and 20 is Sophomore, and 00 is "someone who just applied" or whatever. What's so difficult about changing your codes table so the code_key is a varchar(5)? How is that more work than translating the 5-char codes to 1-char codes and sticking with what I've got. ?? If somebody had given me a dramitcally better way to handle coded fields than what I'm already doing, I might have been willing to re-write everything. But right now, I'm leaning more toward the "if it ain't broke, don't fix it" theory. What happens when you need more than 52 codes? And, um, it IS broke, because the data values don't fit in it. :) If you want to change it to conform to your standards, that's fine, but it sounds like it's you, a DBA, who created this DB (or inherited it) up against a large registrar system, and I'd vote that the latter had more schema design done. No offense, I've worked for a university and I know how fubar'd those things can be. Changing it to your way isn't a BAD idea. I'm not saying "Run screaming!" I'm just saying what I'd do. And of course I have no idea how much code/whatever you'd need to change. But if you changed your system to gracefully accept new codes by having your code_key be varchar(5) that sounds like a win all around. *shrug* Somebody suggested (via private email I think) that I use enum. I had mentioned in my original message that I didn't want to do that because some of the coded fields allow end-users to add/remove codes. But he pointed out that that could be done via alter table commands. That's kind of scary to me. I dunno. Yeah, I've been convinced that altering ENUMs is a bad idea too. -Sheeri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqlmanager logging?
None from me, sad to say. :( -Sheeri On 5/5/06, Duzenbury, Rich <[EMAIL PROTECTED]> wrote: > -Original Message- > From: sheeri kritzer [mailto:[EMAIL PROTECTED] > Sent: Friday, May 05, 2006 3:00 PM > To: Duzenbury, Rich > Cc: mysql@lists.mysql.com > Subject: Re: mysqlmanager logging? > > su - mysql > touch /var/lib/mysql/mysqlmanager.log > > see if that helps; maybe having the file there will kick it into gear. > > Is mysqlmanager actually running? Is there a pid file? > > -Sheeri LX03:~ # ps -ef | grep mysql mysql18706 1 0 May04 ?00:00:00 /usr/sbin/mysqlmanager --user=my sql --pid-file=/tmp/manager.pid And, both my instances are up. Excerpt from /etc/my.cnf: [mysql.server] use-manager [manager] socket=/tmp/manager.sock pid-file=/tmp/manager.pid monitoring-interval=30 LX03:/tmp # ls -al /tmp | grep manager -rw-rw 1 mysql mysql 6 May 5 15:30 manager.pid srwxrwxrwx 1 mysql mysql 0 May 5 15:30 manager.sock Creating the mysqlmanager.log file does not seem to have helped. It's still empty. Any further advice? Thanks. Regards, Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Determine version of *.frm, *.MYD and *.MYI
I think you're thinking of mysqlcheck: (from the documentation) -check-upgrade, -g Invoke CHECK TABLE with the FOR UPGRADE option to check tables for incompatibilities with the current version of the server. This option was added in MySQL 5.0.19. -Sheeri On 5/5/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: This is right off the top of my head so I might be wrong! Does myisamchk not return the mysql version that the table was created with, if you use a very verbose option to check the *.MYI files? You could make a copy of your tables and experiment with myisamchk on the copies, see if that throws any light on things. Regards Keith Roberts In theory, theory and practice are the same; in practice they are not. On Fri, 5 May 2006, sheeri kritzer wrote: > To: Nils Lastein <[EMAIL PROTECTED]> > From: sheeri kritzer <[EMAIL PROTECTED]> > Subject: Re: Determine version of *.frm, *.MYD and *.MYI > > No backups? > > And you compiled it yourself with no records of how you compiled it or > what version you used? what if you want to compile a new version with > the same flags, how would you remember how to do that? > > If those don't help, trial and error is the only way. And you know > it's not 4.1.19, that was JUST released. And you just eliminated one > other one. Only 17 left to go. I wish I could be more helpful, but > when you don't backup or document, that's what happens. :( > > -Sheeri > > On 5/5/06, Nils Lastein <[EMAIL PROTECTED]> wrote: > > > > > > I know it a 4.1... But as I compiled it my self it is not so easy to > > figure > > it out And it might take a while to trial-n-error all 4.1.x > > > > Nils > > > > > > -Oprindelig meddelelse- > > Fra: sheeri kritzer [mailto:[EMAIL PROTECTED] > > Sendt: fr 05-05-2006 20:28 > > Til: Nils Lastein > > Cc: mysql@lists.mysql.com > > Emne: Re: Determine version of *.frm, *.MYD and *.MYI > > > > > > You don't have ANY idea what branch it was created with? 3.2x, 4.0, > > 4.1, 5.0, 5.1 ? I'd recommend finding another similar server in > > your > > dept and see what it's running (assuming there's no standards doc, > > or > > sysadmin to ask, etc). > > > > Do you remember around when the time was that you last > > installed/upgraded mysql on the box? If so you might be able to > > figure out which release it was. > > > > You can try to use something in the same branch -- if it was > > 4.1.something, try the latest in the 4.1 branch. > > > > Where were your backups stored? If you used mysqldump it logged the > > server version at the top of the output file. > > > > -Sheeri > > > > On 5/5/06, Nils Lastein <[EMAIL PROTECTED]> wrote: > > > After a disk crash I managed to save the *.frm, *.MYD and > > > *.MYI-files > > > from the disk. When putting these files into another mysql > > > server I get: > > > > > > mysql> select * from validate; > > > ERROR 1033 (HY000): Table './mydb/validate' was created with a > > > different > > > version of MySQL and cannot be read > > > > > > Unfortunately I'm unable to access the disk anymore, so I > > > cannot see > > > what version of the server generated these. > > > > > > How do I do that? > > > > > > Nils -- 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: mysqlmanager logging?
su - mysql touch /var/lib/mysql/mysqlmanager.log see if that helps; maybe having the file there will kick it into gear. Is mysqlmanager actually running? Is there a pid file? -Sheeri On 5/5/06, Duzenbury, Rich <[EMAIL PROTECTED]> wrote: > -Original Message- > From: sheeri kritzer [mailto:[EMAIL PROTECTED] > Sent: Thursday, May 04, 2006 3:12 PM > To: Duzenbury, Rich > Cc: mysql@lists.mysql.com > Subject: Re: mysqlmanager logging? > > Can the program write to /var/lib/mysql/mysqlmanager.log? > check permissions. > # su - mysql [EMAIL PROTECTED]:~> whoami mysql [EMAIL PROTECTED]:~> cd /var/lib/mysql [EMAIL PROTECTED]:~> touch foo.txt [EMAIL PROTECTED]:~> ls -al total 3 drwxr-xr-x 4 mysql mysql 120 2006-05-05 13:25 . drwxr-xr-x 56 root root 1472 2006-05-05 04:25 .. -rw-r--r-- 1 mysql mysql0 2006-05-05 13:25 foo.txt drwx--x--x 2 mysql mysql 1752 2006-05-01 09:33 mysql drwxr-xr-x 2 mysql mysql 48 2006-05-01 09:33 test I don't think there is a permission problem. Any further ideas? Thanks. Regards, Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Determine version of *.frm, *.MYD and *.MYI
No backups? And you compiled it yourself with no records of how you compiled it or what version you used? what if you want to compile a new version with the same flags, how would you remember how to do that? If those don't help, trial and error is the only way. And you know it's not 4.1.19, that was JUST released. And you just eliminated one other one. Only 17 left to go. I wish I could be more helpful, but when you don't backup or document, that's what happens. :( -Sheeri On 5/5/06, Nils Lastein <[EMAIL PROTECTED]> wrote: I know it a 4.1... But as I compiled it my self it is not so easy to figure it out And it might take a while to trial-n-error all 4.1.x Nils -Oprindelig meddelelse- Fra: sheeri kritzer [mailto:[EMAIL PROTECTED] Sendt: fr 05-05-2006 20:28 Til: Nils Lastein Cc: mysql@lists.mysql.com Emne: Re: Determine version of *.frm, *.MYD and *.MYI You don't have ANY idea what branch it was created with? 3.2x, 4.0, 4.1, 5.0, 5.1 ? I'd recommend finding another similar server in your dept and see what it's running (assuming there's no standards doc, or sysadmin to ask, etc). Do you remember around when the time was that you last installed/upgraded mysql on the box? If so you might be able to figure out which release it was. You can try to use something in the same branch -- if it was 4.1.something, try the latest in the 4.1 branch. Where were your backups stored? If you used mysqldump it logged the server version at the top of the output file. -Sheeri On 5/5/06, Nils Lastein <[EMAIL PROTECTED]> wrote: > After a disk crash I managed to save the *.frm, *.MYD and *.MYI-files > from the disk. When putting these files into another mysql server I get: > > mysql> select * from validate; > ERROR 1033 (HY000): Table './mydb/validate' was created with a different > version of MySQL and cannot be read > > Unfortunately I'm unable to access the disk anymore, so I cannot see > what version of the server generated these. > > How do I do that? > > Nils > > -- > 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: Table so slow to read
1) See if the DNS on one machine is different. If it's trying to resolve the host on one and failing, that might be why it's slow. 2) how did you copy the database? 3) what's the data usage like on each machine? If one machine is used a lot and the other is used very little, then it may be that MySQL's query cache, or the OS cache, is returning the results faster. -Sheeri On 5/4/06, Barry <[EMAIL PROTECTED]> wrote: Gabriel Mahiques schrieb: > My name is Gabriel, "Saludos Cordiales" is the same than "Best Regard" > in spanish. > > > The server explanin is the same. The table structure is the same, the > application is the same (redirect the data source only), the quantity > of record is the same. All is the same, I copy the database from one > server to other > But the servers are not the same, right? This could be one problem. The other problem might be missing indezies on your other server. Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Memory Problem causing mysql to crash
Well, according to my calculations: innodb_buffer_pool_size + key_buffer_size + max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size) + max_connections*2MB (I used the default binlog_cache_size value of 32K plus your settings) MySQL could use up to 4.991913 G of memory. Shouldn't be a problem, unless of course your 8G of machine is running something other than MySQL. Is it? Because the fact that it could not allocate memory means that something was trying to use memory that didn't exist Did MySQL dump a core file? Did you follow this advice? You seem to be running 32-bit Linux and have 473 concurrent connections. If you have not changed STACK_SIZE in LinuxThreads and built the binary yourself, LinuxThreads is quite likely to steal a part of the global heap for the thread stack. Please read http://www.mysql.com/doc/L/i/Linux.html Did you read the man page? The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. Also, did you try to look at your slow query logs to see if there was some kind of query hogging memory? What about backups running at the same time? I'll note that you maxxed out your connections, which shouldn't cause a crash, but might indicate that your server tuning is not up-to-date with your actual usage. Are your data and logfiles are on a diffferent partitions? We had problems with one machine where the data and logfiles were on the same partition, and it would crash -- we moved to a machine that was the same except for the different OS partitions, and it didn't crash! We figure the disk seeking just killed the OS so it segfaulted the mysql process. -Sheeri On 5/4/06, Dobromir Velev <[EMAIL PROTECTED]> wrote: Hi, I'm trying to resolve why InnoDB is crashing. It happened twice for the last month without obvoius reason Any help will be appreciated. Dobromir Velev My Server is Red Hat Enterprise Linux ES release 3 (Taroon Update 7) 2.4.21-32.0.1.ELsmp Dual 3.2 GHz Intel Xeon 8 GB RAM with 3 x 146GB SCA Ultra 320 10K RPM SCSI Drives my.cnf settings innodb_buffer_pool_size=2000M innodb_additional_mem_pool_size=20M innodb_log_file_size=150M innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=0 innodb_lock_wait_timeout=50 key_buffer_size=1000M read_buffer_size=500K read_rnd_buffer_size=1200K sort_buffer_size=1M thread_cache=256 thread_concurrency=8 thread_stack=126976 myisam_sort_buffer_size=64M max_connections=600 The error log shows the following message: InnoDB: Fatal error: cannot allocate 1048576 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 2263507272 bytes. Operating system errno: 12 InnoDB: Cannot continue operation! InnoDB: Check if you should increase the swap file or InnoDB: ulimits of your operating system. InnoDB: On FreeBSD check you have compiled the OS with InnoDB: a big enough maximum process size. InnoDB: We now intentionally generate a seg fault so that InnoDB: on Linux we get a stack trace. mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=1048576000 read_buffer_size=507904 max_used_connections=600 max_connections=600 threads_connected=473 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 1935995 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. You seem to be running 32-bit Linux and have 473 concurrent connections. If you have not changed STACK_SIZE in LinuxThreads and built the binary yourself, LinuxThreads is quite likely to steal a part of the global heap for the thread stack. Please read http://www.mysql.com/doc/L/i/Linux.html thd=(nil) Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0xbff1f558, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x8072d74 0x826d678 0x8213c74 0x8213d04 0x8218b84 0x81d5ba6 0x80fd659 0x826ae2c 0x82a0cda New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. Number of processes running now: 0 060503 16:37:21 mysqld restarted 060503 16:37:21 Can't start server: Bind
Re: blank user names in user table
Rich, anonymous access means that ''@host has access. That is, "blank" at host, as opposed to [EMAIL PROTECTED] To see if anonymous access is allowed, at the command prompt type: mysql -u asdf if you get a mysql login, you have anonymous access. Otherwise you'll get: ERROR 1045 (28000): Access denied for user 'asdf'@'localhost' (using password: NO) The best way is to do: mysql> select host,user,password from mysql.user; and see if any users or passwords are blank. if users are blank, it means anyone can login, and if passwords are blank it means the password isn't set. You can also look at: mysql> show grants for ''@localhost; mysql> show grants for ''@'%'; hope it helps! -Sheeri On 5/4/06, Duzenbury, Rich <[EMAIL PROTECTED]> wrote: Hi all, I've got a database I recently inherited where there are a number of records in the mysql.user table that have no user id. According to the mysql docs, this is supposed to allow guest access, and there is mention of how to turn it off. How does one actually connect in guest mode? My attempts with the mysql client have so far been in vain. How can I ask mysql to log connection attempts by users so I can see if any of these ID's are actually in use? Thank you. Regards, Rich -- 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: Adding a second slave and Load Data from master questions
LOAD DATA FROM MASTER only works for MyISAM tables. Is
tracking.session an InnoDB table?
-Sheeri
On 5/4/06, Hunter Peress <[EMAIL PROTECTED]> wrote:
Hi. im trying to add a second slave using load data from master ,and
it seems to me that when i run this command on the
new slave that its simply picking up from where the first slave is
replicating from. Does this make sense? Also interesting is that
when both the IO and SQL threads are No on the new slave, it
downloads until 661 MB then stops. Another fact is that the first
slave is actually
out of sync.
So i have some questions on load data from master:
is it designed to work with a partway updated slave, or is it only
designed to work from a completely blank database?
Heres the error logs from the new slave im trying to set up.
May 4 09:29:41 localhost mysqld[29920]: 060504 9:29:41
[Note] Slave SQL thread initialized, starting replication in log
'FIRST' at position 0, relay log './djembe-relay-bin.01' position: 4
May 4 09:29:52 localhost mysqld[29920]: 060504 9:29:52
[Note] Slave I/O thread: connected to master '[EMAIL PROTECTED]',
replication started in log 'FIRST' at position 4
May 4 09:29:52 localhost mysqld[29920]: 060504 9:29:52
[ERROR] Slave: Error 'Table 'tracking.session' doesn't exist' on
query. Default database: 'tracking'. Query: 'insert into `session`
(phpsessio
nid, useragent, remoteip, guid, userid, entryurl, referurl,
created) values('10e55f72ff0321de6199df3c650608d3', 'Python-urllib/
1.15', '10.2.1.11', NULL, NULL, '/', NULL, NULL)', Error_code: 1146
May 4 09:29:52 localhost mysqld[29920]: 060504 9:29:52
[ERROR] Error running query, slave SQL thread aborted. Fix the
problem, and restart the slave SQL thread with "SLAVE START". We
stopped at log
'mysql-bin.000256' position 4
May 4 09:30:06 localhost mysqld[29920]: 060504 9:30:06
[ERROR] Slave I/O thread killed while reading event
May 4 09:30:06 localhost mysqld[29920]: 060504 9:30:06
[ERROR] Slave I/O thread exiting, read up to log 'mysql-bin.000256',
position 6268185
Hunter Peress
[EMAIL PROTECTED]
Web Programmer
The New Mexican, Inc.
www.freenewmexican.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: question about utf and collation
I don't know what version of MySQL you're using, but a google search on "mysql croatian" got me: http://bugs.mysql.com/bug.php?id=16373 and http://bugs.mysql.com/bug.php?id=6504 which implies you can use CHARACTER SET latin2 COLLATE latin2_croatian_ci but also shows that it's not quite working yet. Follow those bugs, and you'll find what you want. (note the link at the bottom of one of those bugs: http://www.ambra.rs.ba/ I can't read croatian so I can't tell if that website is of any use). -Sheeri On 5/4/06, Marko Zmak <[EMAIL PROTECTED]> wrote: I'm sorry if this is not the apropriate list, but I couldn't fined any other list where this question would fit in. If someone know where to post it, please suggest. I have a question about collation and utf in mysql. I'm using mysql on several of my websites, but the mysql database doesn't have croatian collation for utf. And most of my sites are in croatian. Is there any plan to add croatian collation for utf, and when? Thanks. -- Marko Žmak, dipl.ing.mat. Mob: +385 98 212 801 Email: [EMAIL PROTECTED] Web: http://www.studioartlan.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: How to convert this DELETE command from MySQL 4.0.25 to 3.23?
You are not being honest with us on the list. Firstly, the error you got: You have an error in your SQL syntax near 'USING USING A RIGHT JOIN B ON B.id = A.sectionid' at line 1 SQL=DELETE FROM A USING A RIGHT JOIN B ON B.id = A.sectionid WHERE B.id is null indicates that you used the USING keyword twice in your query, which won't work in any version of MySQL. I tried to replicate what you have: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1568 to server version: 4.1.12-standard-log mysql> create table A (A int, sectionid int); Query OK, 0 rows affected (0.31 sec) mysql> create table B (id int, A int); Query OK, 0 rows affected (0.23 sec) mysql> select * from A USING A RIGHT JOIN B ON B.id=A.sectionid WHERE B.id is null; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'USING A RIGHT JOIN B ON B.id=A.sectionid WHERE B.id is null' at line 1 As you can see, on MySQL 4.1.12 I'm getting an error. ( I used select * from instead of delete from because that's how I test out delete queries to make sure I don't do something dumb). I think you don't want the "USING A" at all: select * from A RIGHT JOIN B ON B.id=A.sectionid WHERE B.id is null; works just fine for me. And it's true that in 3.23 you could not do a multiple-table UPDATE (that was introduced in 4.0.0). So I'm guessing that's what you really meant. Why are you even bothering, though? why not just use DELETE FROM A WHERE sectionid IS NULL; DELETE FROM B WHERE id IS NULL; ? Because that's all you're really doing in those queries. Not that it needs to be said, but you should upgrade. -Sheeri On 5/4/06, The Nice Spider <[EMAIL PROTECTED]> wrote: >> This query running fine on 4.0.25 but when trying on >> 3.23 an error occurs. >> can one help me to find correct command for 3.23? > Probably if you post the error message you get. > DELETE FROM A USING A RIGHT JOIN B ON B.id = A.sectionid WHERE B.id is null error message on 3.23 is: You have an error in your SQL syntax near 'USING USING A RIGHT JOIN B ON B.id = A.sectionid' at line 1 SQL=DELETE FROM A USING A RIGHT JOIN B ON B.id = A.sectionid WHERE B.id is null BUT this query run ok in 4.0.25. i need to find error free syntaks for 3.23 version. any help? __ 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: importing a dumpfile from with the mysql client
On 5/4/06, Luke Vanderfluit <[EMAIL PROTECTED]> wrote: [snip] I started this process remotely then went to the site to finish it. But when the dump finished (several hours later) I was not able to execute the following commands from my original location. mysql> SET FOREIGN_KEY_CHECKS = 1; mysql> COMMIT; What do you mean "you were not able"? Did you get an error? Was the server hung? Did the keyboard stick to make you unable to type the commands? My question is: Since the import completed the database has grown in size and been backed up etc. Yet from the original session I have not executed those 2 commands. Is it safe to execute them? Or would executing them cause corruption or other unforseen stuff? Is it unnecessary to execute them? Is it safe to kill that original mysql session? Those 2 commands, in and of themselves, aren't dangerous. It's what's in the script that could be the problem. However, since you really didn't mention what error you got, it's not easy to figure out how to fix it. It depends what tables you're running these on. If you're running them on MyISAM tables, deadlocking cannot happen. Then again, transactions are meaningless too. If you're running on InnoDB you have the possibility of deadlocking, but MySQL is pretty good about avoiding that. If other users/applications are writing to the SAME data then there's the possibility of inconsistent data, but I wouldn't expect corruption. if you post what errors you actually got, I might be able to help. Also, are you using the same db and tables that other apps/users are using? Does the dumpfile contain "DROP TABLE IF EXISTS" commands? -Sheeri -- Luke Vanderfluit. Analyst/Programmer. Internode Systems Pty. Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql memory utilization
The total possible memory MySQL will use is: innodb_buffer_pool_size + key_buffer_size + max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size) + max_connections*2MB Someone please correct me if I'm wrong. Are your most heavily used tables innodb? If not, then you're not looking at the right fields. You're doing a TON of reads per second, I'm impressed. 6.65 inserts/s, 370.98 updates/s, 0.00 deletes/s, 163055.10 reads/s You might want to check your slow query logs and use EXPLAIN on the queries to see if an index will help you use less memory (ie, if you're doing a full table scan). Total memory allocated 61180; in additional pool allocated 20954624 So you have about 583 M of memory allocated. That's not a lot, given the huge amount of reads you're doing. And your additional pool gives you about 2M more. I just did some stats on one of our medium-use servers: 7.00 inserts/s, 227.89 updates/s, 2.00 deletes/s, 14198.90 reads/s Total memory allocated 4753221600; in additional pool allocated 33100800 That's almost 4.5G of memory (and we need it all) allocated with an additional pool of 31M. From your stats: Buffer pool size 32768 Free buffers 0 This means you're using ALL your buffers! From our medium use server: Buffer pool size 262144 Free buffers 37108 (and we peak Sun and Mon nights, so having free buffer space now is a good thing). What is innodb_buffer_pool_size set to? (ours is 4G) I'm betting that needs to be changed. -Sheeri On 5/5/06, Anil <[EMAIL PROTECTED]> wrote: Hi list, We are facing memory problems for our application and to analyze memory utilization by application we require below information on mysql memory utilization. how much of RAM mysql is utilizing and amount of memory allocated for innodb buffer is utilizing ,how much of innodb buffer is free and amount of thread based buffers allocated. We are getting some information with " show innodb status " please let me know how to interpret the output . I am pasting output of " show innodb status " below for reference. Please give us detailed explanation how to interpret the output. = 060504 20:00:01 INNODB MONITOR OUTPUT = Per second averages calculated from the last 20 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 522818462, signal count 504180146 Mutex spin waits 1363737867, rounds 305233322, OS waits 106732853 RW-shared spins 740792501, OS waits 360301857; RW-excl spins 45582064, OS waits 16212977 FILE I/O I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 383418129 OS file reads, 59580243 OS file writes, 9957395 OS fsyncs 10.10 reads/s, 27901 avg bytes/read, 3.30 writes/s, 2.10 fsyncs/s - INSERT BUFFER AND ADAPTIVE HASH INDEX - Ibuf for space 0: size 1, free list len 506, seg size 508, is empty Ibuf for space 0: size 1, free list len 506, seg size 508, 13076970 inserts, 13077112 merged recs, 2977056 merges Hash table size 2212699, used cells 679260, node heap has 796 buffer(s) 1161.94 hash searches/s, 984.85 non-hash searches/s --- LOG --- Log sequence number 89 54255649 Log flushed up to 89 54141193 Last checkpoint at 89 30596518 0 pending log writes, 0 pending chkp writes 2089244 log i/o's done, 1.30 log i/o's/second -- BUFFER POOL AND MEMORY -- Total memory allocated 61180; in additional pool allocated 20954624 Buffer pool size 32768 Free buffers 0 Database pages 31972 Modified db pages 5217 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 1455127697, created 6339651, written 91917358 17.20 reads/s, 5.10 creates/s, 18.00 writes/s Buffer pool hit rate 1000 / 1000 -- ROW OPERATIONS -- 2 queries inside InnoDB, 0 queries in queue Main thread process no. 12278, id 28680, state: sleeping Number of rows inserted 172622749, updated 475137381, deleted 45859392, read 4138993176 6.65 inserts/s, 370.98 updates/s, 0.00 deletes/s, 163055.10 reads/s END OF INNODB MONITOR OUTPUT Thanks Anil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Coded fields
John,
You're close -- If a single case-sensitive letter won't help (or isn't
descriptive enough -- is 'S' sophomore or senior or special student?)
The best way to do it is NOT to make new codes. If they're giving you
codes, use theirs -- why make up a new system if you don't have to?
create table codes (
code_type varchar(10,
code_key varchar(5),
code_text varchar(80)
);
and then you can change your own codes from 's' and 'S' to 'soph' and
'sen' or whatever. Or just use whatever they use for those statuses.
Of course, if you're dealing with registrars from different schools
who may use the same code for 2 different things, then you might want
to use a surrogate key (ie, autoincrement field). But if they're
giving you special codes, why make up your own?
-Sheeri
On 5/4/06, John Heim <[EMAIL PROTECTED]> wrote:
What is the best way to create a coded field? I want to do something
similar to enumeration but I don't want to have to define the values at
table creation time because sometimes the end-users need to add or remove
the codes.
I've been using char binary fields in my database to this point figuring
that takes only one byte per stored code. Then the values can be ASCII
chars and would be kind of meaningful if retrieved from the database. For
instance, I might have 'f' for freshman, 'S' for Sophomore, 'J' for Junior,
and 's' for Senior. Actually, there's like 20 different categories for
students. So then I have a lookup table for codes:
create table codes (
code_type varchar(10,
code_key char binary,
code_text varchar(80)
);
Then I can do left joins to retrieve a description of the code if
necessary. For example:
INSERT INTO codes VALUES ('class', 'F', 'Freshman');
INSERT INTO codes VALUES (class', 'S', 'Sophomore');
INSERT INTO codes VALUES ('class', 'J', 'Junior');
INSERT INTO codes ('class', 's', 'Senior');
SELECT C.code_text AS academic_class
FROM students S
LEFT JOIN codes C ON (C.code_type='class' AND S.class=C.code_key);
That particular example might work better with enum but it's a deliberately
trivialized example. Most of my coded fields have 5-20 possible values.
My problem is that I've had some codes imposed upon me that are 5 chars. I
don't know if I should just start over or what. Maybe other people deal
with coded fields in a totally different way that is way better than what
I've invented.
Suggestions?
--
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: Searching a large table
Sounds like you want LIMIT and OFFSET -- everything after my name and before your post is copied from the doc at http://dev.mysql.com/doc/refman/4.1/en/select.html (or am I misunderstanding your question?) -Sheeri 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 both be non-negative integer constants (except when using prepared statements). 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): SELECT * FROM tbl LIMIT 5,10; # Retrieve rows 6-15 To retrieve all rows from a certain offset up to the end of the result set, you can use some large number for the second parameter. This statement retrieves all rows from the 96th row to the last: SELECT * FROM tbl LIMIT 95,18446744073709551615; With one argument, the value specifies the number of rows to return from the beginning of the result set: SELECT * FROM tbl LIMIT 5; # Retrieve first 5 rows In other words, LIMIT row_count is equivalent to LIMIT 0, row_count. For prepared statements, you can use placeholders (supported as of MySQL version 5.0.7). The following statements will return one row from the tbl table: SET @a=1; PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?'; EXECUTE STMT USING @a; The following statements will return the second to sixth row from the tbl table: SET @skip=1; SET @numrows=5; PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?, ?'; EXECUTE STMT USING @skip, @numrows; For compatibility with PostgreSQL, MySQL also supports the LIMIT row_count OFFSET offset syntax. On 5/5/06, Steve <[EMAIL PROTECTED]> wrote: All: I am developing a search engine and using MySQL as the backend database management system. Under normal circumstances, when users search through large volumes of records on a search engine site, the results are broken down into pages. When a user clicks on the 'Next' link, the system will re-query the database and return those records that pertain to that page (records 10 through 20, perhaps). This, as you can plainly see, requires the application to re-query the same data from the same table each time the user clicks on a 'Next' or 'Previous' link. I would imagine that this may lead to some resource contention on that main table, especially when many users are using the system simultaneously. I've never seen vBulletin's code, but I have been able to determine from careful analysis (and testing) that they employ a search table, of sorts, that contains the returned records from a search. So, when a user of the system submits a search query, the system returns the records and throws them into a separate search table, identified by a sequential primary key field. Then, the system uses that search table to display the appropriate records according to the respective search identifier (referenced in the URL), never touching the main table again until a brand new search is performed. This seems to be a pretty good way to facilitate large text-based searches. Are there any other mechanisms that can be used to build a powerful, yet quick and light on resources, search system? Is a fulltext index the best way to achieve maximum performance with this kind of search? Thanks. -- Steve - Web Applications Developer http://www.sdwebsystems.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: Group-wise maximum
Try looking at the documentation for "groupwise maximum". http://dev.mysql.com/doc/refman/4.1/en/example-maximum-column-group-row.html -Sheeri On 5/5/06, Ian Klassen <[EMAIL PROTECTED]> wrote: Hello, I'm working on using a temporary table to get the group-wise maximum for a number of items. I have a table structure like: CREATE TABLE item ( item_id int not null primary key, name varchar(100) not null); CREATE TABLE attribute_a ( index (item_id, time_id), item_id int not null, time_id datetime not null, value int not null); Each item has many attributes. The time_id of an attribute contains the time of the /last /update to the attribute. My objective is to return the the attribute's value for each item at a requested time. I believe the best solution is to get the maximum time_id's (<= requested time) and dump them into a temporary table. Then use these times to retrieve the actual values. My problem is that as I add a number of attributes the query time bogs down when creating the temporary table. The query looks something like: CREATE TEMPORARY TABLE times (INDEX (item_id)) SELECT item.item_id, MAX(attribute_a.time_id) AS attribute_a_time FROM item LEFT JOIN attribute_a ON (attribute_a.item_id = item.item_id AND attribute_a.time_id < '2000-10-01 00:00') GROUP BY item.item_id; I add a LEFT JOIN for each attribute that I want to retrieve (up to 60). Let's say each attribute table contains 10,000 rows (all with dates less than '2000-10-01'). If I understand things correctly, using this query 10,000 rows would be added for each attribute that I joined. If I added 60 attributes MySQL would have to handle grouping 600,000 rows! Is that correct? Is there a better solution? I tried using sub queries but that didn't seem to be much faster. If I had 1,000 items to search for 60 attributes the sub queries would be called 60,000 times. Any ideas? Thanks! Ian -- 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: Determine version of *.frm, *.MYD and *.MYI
You don't have ANY idea what branch it was created with? 3.2x, 4.0, 4.1, 5.0, 5.1 ? I'd recommend finding another similar server in your dept and see what it's running (assuming there's no standards doc, or sysadmin to ask, etc). Do you remember around when the time was that you last installed/upgraded mysql on the box? If so you might be able to figure out which release it was. You can try to use something in the same branch -- if it was 4.1.something, try the latest in the 4.1 branch. Where were your backups stored? If you used mysqldump it logged the server version at the top of the output file. -Sheeri On 5/5/06, Nils Lastein <[EMAIL PROTECTED]> wrote: After a disk crash I managed to save the *.frm, *.MYD and *.MYI-files from the disk. When putting these files into another mysql server I get: mysql> select * from validate; ERROR 1033 (HY000): Table './mydb/validate' was created with a different version of MySQL and cannot be read Unfortunately I'm unable to access the disk anymore, so I cannot see what version of the server generated these. How do I do that? Nils -- 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: Is the Optimizer on 5.0.20 broken?
You're comparing apples to oranges -- your where clauses are
different. The first query uses the primary key because you're
actually comparing 'account' to something. The second query doesn't
because you're using the 'street' field -- what does SHOW INDEXES
FROM account; show? any indexes on 'street', in 4.1 that didn't get
moved to 5.0?
-Sheeri
On 5/5/06, Juri Shimon <[EMAIL PROTECTED]> wrote:
Hello All,
I have a next problem. After upgrade from 4.1 branch to 5.0,
the productivity of our applications has decreases too much.
Below is a tipical case.
Table 'account' has primary(department,account) and 40777 records.
Table 'payment' primary(department,account,year,month) and 3831797 records.
In this case all ok ('where' uses primary key of table 'account'):
> desc select * from
> account a inner join payment p using(department,account)
> where a.department='1' and a.account=1000
++-+---+--+-+-+-++---+-+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
++-+---+--+-+-+-++---+-+
| 1 | SIMPLE | a | ref | PRIMARY,account | account | 4 | const
| 1 | |
| 1 | SIMPLE | p | ref | PRIMARY | PRIMARY | 10 |
HAS.a.department,const | 28738 | Using where |
++-+---+--+-+-+-++---+-+
This case not working in 5.0.20 ('where' uses index of 'account' with
10 resulting rows):
> desc select * from
> account a inner join payment p using(department,account)
> where a.street=10
++-+---+--+-+-+-++-+-+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows| Extra |
++-+---+--+-+-+-++-+-+
| 1 | SIMPLE | p | ALL | PRIMARY | | |
| 3831797 | |
| 1 | SIMPLE | a | ref | PRIMARY,account | PRIMARY | 10 |
HAS.p.department,HASVODA.p.account | 1 | Using where |
++-+---+--+-+-+-++-+-+
I don't think that selecting for fullscan of table with 3.5 million
rows (instead of table with 40 thousends) is a good idea.
Is this a known bug?
WBR!
Juri.
--
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: How to see why client got blocked
If your server has log-warnings set to ON, you can check the error logs, and use a script to count how many times for each host, in a row, this happens. -Sheeri On 5/5/06, Kishore Jalleda <[EMAIL PROTECTED]> wrote: On 5/5/06, Dominik Klein <[EMAIL PROTECTED]> wrote: > I experience that my slave gets blocked after a while (a couple of slave > stop and slave start happen in the meantime). In errorlog I see > > Slave I/O thread: error reconnecting to master > '[EMAIL PROTECTED]:3306': Error: 'Host 'myhost.mydomain.de' is > blocked because of many connection errors; unblock with 'mysqladmin > flush-hosts'' errno: 1129 retry-time: 60 retries: 86400 > > Slave and Master are 5.0.20. > How can I see why the slave was blocked? > > Regards > Dominik > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > What is your max_connect_errors setting on the master, because your slave has failed to connect to the master for more than max_connect_errors times, please look at http://dev.mysql.com/doc/refman/5.0/en/blocked-host.html.. Do you also see numerous failed connects in your slave log, along with the blocked error. >another question on this error message: > >is it possible to see the count of errors for each host from some table >or file? I don't think you can do this, but you can write a shell/perl script to parse your error logs and count the errors for each host. Kishore Jalleda http://kjalleda.googlepages.com/projects -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL server has gone away. Suddenly.
Do a "show status like 'uptime'" after the script runs. See if the server crashed (if so, uptime will be low) -- if the server crashed you might get that error. show variables like "%connections" should show you how many connections you can have per user and total. That shouldn't be the problem; you'd get a "too many connections" error if that was the problem, but it couldn't hurt to check. show grants for your user to see if you have any limits on your user resources: http://dev.mysql.com/doc/refman/4.1/en/user-resources.html again, the errors would be different. What's max_allowed_packet set to? You said you can run the query just fine -- did you just try on commandline, or can you run the query in a script by itself? What happens if you run the script to echo all the SQL commands into a text file, and then source the text file from the mysql commandline prompt? Same error? Are the script and the host on the same machine? Is it using TCP/IP to connect, or a unix socket? Is there any firewalling in place? Does anyone else have admin privileges to the database? They might be manually killing the query, if it hangs up. (I've had this done to me, where an admin kept killing long queries without asking folks who was doing them). Are you working on an InnoDB table? Try turning on the InnoDB monitor while the query runs and see if you're getting any deadlocking. http://dev.mysql.com/doc/refman/5.0/en/innodb-monitor.html -Sheeri On 5/5/06, Sander Smeenk <[EMAIL PROTECTED]> wrote: Quoting Kishore Jalleda ([EMAIL PROTECTED]): > >Can anyone shed any light on this issue? > This might shed more light into your problem > http://dev.mysql.com/doc/refman/5.0/en/gone-away.html I've read that :) But still, the query returns the same data, if I run it alone, or in the complete stats script. Please tell me (how i can find out) why the script bails out when i run alot of other queries in front of it, and why it works when i run just that query? Actually none of the 'reasons' listed at the url you gave me, really apply to my situation. Except maybe the "You can also get these errors if you send a query to the server that is incorrect or too large." topic. But still, explain to me, why DOES it work when i run just that query, and why DOESN'T it work when alot of other queries were in front of it... :) Sander. -- | Depression is merely anger without enthusiasm. | 1024D/08CEC94D - 34B3 3314 B146 E13C 70C8 9BDB D463 7E41 08CE C94D -- 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: Subselect application
http://www.xaprb.com/blog/2006/04/30/how-to-optimize-subqueries-and-joins-in-mysql/ Is a good article. -Sheeri On 4/21/06, Chris White <[EMAIL PROTECTED]> wrote: I was looking around the list search and didn't find much on this subject (maybe didn't look back far enough), but I was discussing with a coworker about a reasonable application of subselects vs. a WHERE clause or table join. Thank you in advance. -- Chris White Interfuel -- 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: ORDER BY making recordset non-updatable
Maybe I'm thick You have a view, called vwMyView. You SELECT rows from it, and you're able to update the view? Yet when you SELECT with an ORDER BY clause, you're not allowed to update the view? I just do not understand how a read statement affects DML. I think you're going to have to post the query you're using, as it's more complex than a SELECT. Perhaps you're using a REPLACE INTO SELECT statement? Or UPDATE WHERE IN (SELECT)? -Sheeri On 4/19/06, Eland, Travis M. <[EMAIL PROTECTED]> wrote: Heya. I am in the process of modifying a program to access data from a MySQL database instead of a SQL Server database. I have a view that is referenced as follows (through use of a data environment command): Select * from vwMyView where id = ? If I run this command, I get the data that I would expect, and I am able to update the data that I would expect to update (there are a few joins in the view so there are a couple fields that I understand that I cannot update). My problem is, if I add an ORDER BY statement at the end of this command, the recordset still returns data, but it becomes non-updatable. I would include my SQL, but unfortunately it is on a classified machine. I have verified the SQL numerous times and it works fine in every way except when I use ORDER BY. The SQL structure (though slightly modified for mySQL) also worked fine in SQL Server. Is this a known issue? Is there something that I could possibly be missing? I apologize for the lack of actual code, but I appreciate any insight! Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqlmanager logging?
Can the program write to /var/lib/mysql/mysqlmanager.log? check permissions. On 4/19/06, Duzenbury, Rich <[EMAIL PROTECTED]> wrote: MySQL 5.0.20 I've got two instances running with mysqlmanager. I'm not getting any logging of any sort. mysqlmanager --help shows: - - log /var/lib/mysql/mysqlmanager.log pid-file /tmp/manager.pid socket/tmp/manager.sock bind-address (No default value) port 2273 password-file /etc/mysqlmanager.passwd default-mysqld-path /usr/sbin/mysqld monitoring-interval 10 run-as-serviceFALSE user (No default value) wait-timeout 28800 I've also tried to set the --log option in the [manager] section of /etc/my.cnf and restarted the server, to no avail. Do I have to run-as-service to get logging? Thanks for any help you can offer. Regards, Rich -- 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: ldd "undefined symbol" error
I'd check to make sure you installed mysql-devel, which includes the libraries and header files. -Sheeri On 4/18/06, tuxlinsecure <[EMAIL PROTECTED]> wrote: Hi, I uninstalled Mysql 3.23 (tar.gz )and installed Mysql 5.0 (rpm) on my server. When I check shared lib dependencies using the following command i get "undefined symbol" Any Clues? Thanks, ldd -r /usr/share/Fazzt/lib/libScriptMySQL.so.0.0.15 libKCScriptData.so.0 => /usr/share/Fazzt/lib/libKCScriptData.so.0 (0x00b14000) libKCFC.so.0 => /usr/share/Fazzt/lib/libKCFC.so.0 (0x0052e000) libKOS.so.1 => /usr/share/Fazzt/lib/libKOS.so.1 (0x00111000) libmysqlclient_r.so.15 => not found libz.so.1 => /usr/lib/libz.so.1 (0x002f1000) libcrypt.so.1 => /lib/libcrypt.so.1 (0x0072c000) libnsl.so.1 => /lib/libnsl.so.1 (0x004fc000) libpthread.so.0 => /lib/tls/libpthread.so.0 (0x00d57000) libstdc++-libc6.2-2.so.3 => /usr/lib/libstdc++-libc6.2-2.so.3 (0x00ef1000) libm.so.6 => /lib/tls/libm.so.6 (0x0026d000) libc.so.6 => /lib/tls/libc.so.6 (0x00117000) /lib/ld-linux.so.2 => /lib/ld-linux.so.2 (0x00de2000) libgcc_s.so.1 => /lib/libgcc_s.so.1 (0x0088c000) undefined symbol: mysql_field_count (/usr/share/Fazzt/lib/libScriptMySQL.so.0.0.15) undefined symbol: mysql_store_result (/usr/share/Fazzt/lib/libScriptMySQL.so.0.0.15) undefined symbol: mysql_fetch_row (/usr/share/Fazzt/lib/libScriptMySQL.so.0.0.15) undefined symbol: mysql_affected_rows (/usr/share/Fazzt/lib/libScriptMySQL.so.0.0.15) undefined symbol: mysql_init(/usr/share/Fazzt/lib/libScriptMySQL.so.0.0.15) undefined symbol: mysql_fetch_fields (/usr/share/Fazzt/lib/libScriptMySQL.so.0.0.15) undefined symbol: mysql_close (/usr/share/Fazzt/lib/libScriptMySQL.so.0.0.15) undefined symbol: mysql_num_rows (/usr/share/Fazzt/lib/libScriptMySQL.so.0.0.15) undefined symbol: mysql_error (/usr/share/Fazzt/lib/libScriptMySQL.so.0.0.15) undefined symbol: mysql_num_fields (/usr/share/Fazzt/lib/libScriptMySQL.so.0.0.15) undefined symbol: mysql_free_result (/usr/share/Fazzt/lib/libScriptMySQL.so.0.0.15) undefined symbol: mysql_query (/usr/share/Fazzt/lib/libScriptMySQL.so.0.0.15) undefined symbol: mysql_real_connect (/usr/share/Fazzt/lib/libScriptMySQL.so.0.0.15) - Yahoo! Messenger with Voice. Make PC-to-Phone Calls to the US (and 30+ countries) for 2¢/min or less. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
