Re: Backup and recovery problems
Paul Nowosielski wrote: Dear all, I've been testing our backup and recovery strategies here at work. When dumping all the databases I'm using this command: mysqldump --all-databases --force -u root -p -h 192.168.45.7 all.sql When this command is run I receive these error messages: mysqldump: mysqldump: Couldn't execute 'show create table `help_category`': Can't find file: './mysql/help_category.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `help_keyword`': Can't find file: './mysql/help_keyword.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `help_relation`': Can't find file: './mysql/help_relation.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `help_topic`': Can't find file: './mysql/help_topic.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `proc`': Can't find file: './mysql/proc.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `procs_priv`': Can't find file: './mysql/procs_priv.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `time_zone`': Can't find file: './mysql/time_zone.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `time_zone_leap_second`': Can't find file: './mysql/time_zone_leap_second.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `time_zone_name`': Can't find file: './mysql/time_zone_name.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `time_zone_transition`': Can't find file: './mysql/time_zone_transition.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `time_zone_transition_type`': Can't find file: './mysql/time_zone_transition_type.frm' (errno: 13) (1017) When I recover the databases using this command: mysql --force -u root -p -h localhost all.sql Tables are missing (and not the ones the dump is complaining about) and relations are broken. I'm assuming that the dump is dying before all the data is received. Can anyone give me advice on how to obtain a clean dump with all the tables so I may sleep well at night? I'm using MySQL 4.0.26 client and sever for these databases. I had thought that using the --force switch would alleviate my concerns. Unfortunately not... Best regards, perror 13 Error code 13: Permission denied Mysql does not have permiission to read its own tables. Perhaps you have been testing recovery strategies as root, and have changed the permissions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup and recovery problems
Paul Nowosielski wrote: On Thursday 25 May 2006 12:09, you wrote: Paul Nowosielski wrote: Dear all, I've been testing our backup and recovery strategies here at work. When dumping all the databases I'm using this command: mysqldump --all-databases --force -u root -p -h 192.168.45.7 all.sql When this command is run I receive these error messages: mysqldump: mysqldump: Couldn't execute 'show create table `help_category`': Can't find file: './mysql/help_category.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `help_keyword`': Can't find file: './mysql/help_keyword.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `help_relation`': Can't find file: './mysql/help_relation.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `help_topic`': Can't find file: './mysql/help_topic.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `proc`': Can't find file: './mysql/proc.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `procs_priv`': Can't find file: './mysql/procs_priv.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `time_zone`': Can't find file: './mysql/time_zone.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `time_zone_leap_second`': Can't find file: './mysql/time_zone_leap_second.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `time_zone_name`': Can't find file: './mysql/time_zone_name.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `time_zone_transition`': Can't find file: './mysql/time_zone_transition.frm' (errno: 13) (1017) mysqldump: mysqldump: Couldn't execute 'show create table `time_zone_transition_type`': Can't find file: './mysql/time_zone_transition_type.frm' (errno: 13) (1017) When I recover the databases using this command: mysql --force -u root -p -h localhost all.sql Tables are missing (and not the ones the dump is complaining about) and relations are broken. I'm assuming that the dump is dying before all the data is received. Can anyone give me advice on how to obtain a clean dump with all the tables so I may sleep well at night? I'm using MySQL 4.0.26 client and sever for these databases. I had thought that using the --force switch would alleviate my concerns. Unfortunately not... Best regards, perror 13 Error code 13: Permission denied Mysql does not have permiission to read its own tables. Perhaps you have been testing recovery strategies as root, and have changed the permissions? Ok this is very strange. I checked the permissions on the data directory and they where set to 660(rw rw X) and owned by mysql. Which is wrong. You need 770 or you have no execute (search) privilege on the directory. I became the mysql user and listed the data/mysql directory: [EMAIL PROTECTED]:/usr/local/src/mysql_current/data ll mysql/ and I got: /bin/ls: mysql/procs_priv.MYD: Permission denied /bin/ls: mysql/procs_priv.MYI: Permission denied /bin/ls: mysql/procs_priv.frm: Permission denied /bin/ls: mysql/help_keyword.MYD: Permission denied /bin/ls: mysql/help_keyword.MYI: Permission denied /bin/ls: mysql/help_keyword.frm: Permission denied /bin/ls: mysql/func.MYD: Permission denied /bin/ls: mysql/func.MYI: Permission denied /bin/ls: mysql/func.frm: Permission denied /bin/ls: mysql/columns_priv.MYD: Permission denied /bin/ls: mysql/columns_priv.MYI: Permission denied /bin/ls: mysql/columns_priv.frm: Permission denied /bin/ls: mysql/tables_priv.MYD: Permission denied /bin/ls: mysql/tables_priv.MYI: Permission denied /bin/ls: mysql/tables_priv.frm: Permission denied /bin/ls: mysql/help_topic.MYD: Permission denied /bin/ls: mysql/help_topic.MYI: Permission denied /bin/ls: mysql/help_topic.frm: Permission denied /bin/ls: mysql/time_zone_transition_type.MYD: Permission denied /bin/ls: mysql/time_zone_transition_type.MYI: Permission denied /bin/ls: mysql/time_zone_transition_type.frm: Permission denied /bin/ls: mysql/time_zone_leap_second.MYD: Permission denied /bin/ls: mysql/time_zone_leap_second.MYI: Permission denied /bin/ls: mysql/time_zone_leap_second.frm: Permission denied /bin/ls: mysql/db.MYD: Permission denied /bin/ls: mysql/db.MYI: Permission denied /bin/ls: mysql/db.frm: Permission denied /bin/ls: mysql/host.MYD: Permission denied /bin/ls: mysql/host.MYI: Permission denied /bin/ls: mysql/host.frm: Permission denied /bin/ls: mysql/help_relation.MYD: Permission denied /bin/ls: mysql/help_relation.MYI: Permission denied /bin/ls: mysql/help_relation.frm: Permission denied /bin/ls: mysql/time_zone.MYD: Permission denied /bin/ls: mysql/time_zone.MYI: Permission denied /bin/ls: mysql/time_zone.frm: Permission denied /bin/ls: mysql/proc.MYD: Permission denied /bin/ls: mysql/proc.MYI: Permission denied /bin/ls: mysql/proc.frm: Permission denied /bin/ls: mysql/user.MYD:
Re: I can't connect to mysql server with PHP
战芳 wrote: Hi! gerald_clark, But when I call mysql_pconnect(localhost:3306,root,root_password),it return the same error. How can I get the permission to open /var/mysql/lib/mysql.sock? Fang what do you get when you do ls -l /var/mysql/lib/mysql.sock? Permissions should be srwxrwxrwx fool.ben wrote: Hi everybody! I've install a mysql server on my computer. The operating system is Redhat fedora core 4. The version of the mysql server is 4.1.3 Beta. I wanna connect to the server using the following statement: $db_connection=mysql_pconnect(localhost,root,); The server returned the error 2002: 2002 Can't connect to local MySQL server through socket '/var/mysql/lib/mysql.sock(13)' The user running the php connection does not have permissions to open /var/mysql/lib/mysql.sock. I was suggested that the server may not running, or the sockect is wrong. But when I run the following statement, there was no error occured: localhost#/usr/local/mysql/bin/mysql -uroot -S/var/mysql/lib/mysql.sock Here you are root, so you have permissions. Can anyone help me? Fix your permissions or move your socket to a directory that is world searchable. Fang -- 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 problems under 5.1.9
Ben Clewett wrote: Dear MySQL, I've installed 5.1.9 from source on a SUSE 10 box. But I can't get InnoDB tables respected. I have used the correct compilation flag (--with-innodb). SHOW VARIABLES; lists all the usual innodb variables. The innodb table space has been created in ~/var/ibdata1. But if I enter: CREATE TABLE a ( a int NOT NULL PRIMARY KEY ) ENGINE=InnoDB; SHOW CREATE TABLE a; CREATE TABLE `a` ( `a` int(10) NOT NULL PRIMARY KEY ) ENGINE=MyISAM As you can see, an InnoDB has become an MyISAM and will be stored in ~/var/test/a.* I am using the large table .cnf file. Everything else is much as default. Can anybody help me? Regards, Ben make sure you don't have skip--innodb in your my.cnf file. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance Available
Jan Gomes wrote: Hy Jay, Thanks for you attention. Show you my structure and EXPLAIN: CREATE TABLE `table` ( `id_table1` int(10) unsigned NOT NULL default '0', `id_table2` int(10) unsigned NOT NULL default '0', `field1`smallint(5) unsigned NOT NULL default '0', `field2`mediumint(8) unsigned NOT NULL default '0', `textField` text NOT NULL, PRIMARY KEY (`id_table1`,`id_table2`), KEY `table_idx1` (`id_table2`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=1 You don't show the query you are explaining. It certainly can't be the one in your previous post below, because column names don't match. +-+---+---++ | select_type | table | type | possible_keys | +-+---+---++ | SIMPLE | table | range | PRIMARY,table_idx1| +-+---+---++ ++-+--+--+-+ | key | key_len | ref | rows | Extra | +--- +-+--+--+-+ | table_idx1 | 4 | NULL | 72 | Using where| ++-+--+--+-+ Please post your exact table schema using SHOW CREATE TABLE, and your exact query, along with an EXPLAIN SELECT for the query. Thanks! -jay Jan Gomes wrote: Hy Guys, I have a simple structure of tables, howewer has 50 million of registers and 2,5 GB of data. The table is MyIsam and has 4 integer fields and 1 Text field, one primary key with two integer fields and one btree index with one integer field. There is a select in this table using an index(with one integer field), whith a set value for this field ( select * from table where field in (value1,value2,value3,value4,etc) ). This select has delay 4s average. Is this a good time for the select ? How can I run this select in less time? I had make this optimization: 1-Compress the index 2-sort the index with myisamchk PS.: This table is read-only, hasn't an insert, update or delete. == Atenciosamente, Jan Gomes - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: I can't connect to mysql server with PHP
fool.ben wrote: Hi everybody! I've install a mysql server on my computer. The operating system is Redhat fedora core 4. The version of the mysql server is 4.1.3 Beta. I wanna connect to the server using the following statement: $db_connection=mysql_pconnect(localhost,root,); The server returned the error 2002: 2002 Can't connect to local MySQL server through socket '/var/mysql/lib/mysql.sock(13)' The user running the php connection does not have permissions to open /var/mysql/lib/mysql.sock. I was suggested that the server may not running, or the sockect is wrong. But when I run the following statement, there was no error occured: localhost#/usr/local/mysql/bin/mysql -uroot -S/var/mysql/lib/mysql.sock Here you are root, so you have permissions. Can anyone help me? Fix your permissions or move your socket to a directory that is world searchable. Fang -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Converting varchar field into primary key
Jonathan Mangin wrote: I'm creating a new MySQL database from an existing Filemaker db. My problem is that some of the existing 'numbers' in one column (it was a text field in FMP) have leading zeros. eg: 003, 0007, 012, 001234. I need to maintain these numbers 'as is' - complete with zeros. I've tried all the numeric data types and they all seem to strip these leading zeros. Is it possible to have a numeric field type which will maintain those zeros? I want this column to become the primary key. leading zeros is a typical display requirement. An integer value does not have something as leading zeros. Why not make the VARCHAR the PK and keep the datatype? Martijn Tonies Database Workbench - development tool for MySQL, and more! The manual states (among other things) declaring a column: int(6) zerofill will left pad the column with zeros. 003 will become 03. This won't work with his 3, 4, and 6 character examples. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stumped again by joins
Chris Sansom wrote: At 15:56 +0200 25/4/06, Barry wrote: And you don't see any misdone queries when you echo them, right? Hope you checked that. Hi Barry I was wrong about its being a PHP issue: it's definitely a MySQL error. I realised I hadn't handled the error in such a way that I could see what it was, but now I have, so... The full query, in all its hideousness (but prettied up a bit in the formatting :-) ) is: -- select count(distinct uid) as c from aptg_guides_restricted as r, aptg_guides as g left join guides_biography as b on b.guide_id = r.uid left join guides_interests as i on i.guide_id = r.uid left join guides_tours as t on t.guide_id = r.uid left join guides_walks as w on w.guide_id = r.uid left join guides_lectures as l on l.guide_id = r.uid where g.guide_uid = r.uid and show_on_web = '1' and (b.biography like '%london%' or i.interests like '%london%' or t.tours like '%london%' or w.walks like '%london%' or l.lectures like '%london%') -- and the error I get back is: Unknown column 'r.uid' in 'on clause' ...but I can assure you there is definitely a 'uid' column in aptg_guides_restricted. If I take out the 'r.' from those left joins (there's no uid in any other table mentioned here) I get basically the same error: Unknown column 'uid' in 'on clause'. And if I spell out 'aptg_guides_restricted.uid' in the joins I /still/ get the error: Unknown column 'aptg_guides_restricted.uid' in 'on clause' So what /is/ the problem here? I say again: this and /exactly/ this worked perfectly in MySQL 3.23, so there's obviously some change in syntax handling or whatever between versions. Yes. 3.23 was not correct in the order of precedence. This has been answered many times here. You need to change your comma join to an inner join. select count(distinct uid) as c from aptg_guides_restricted as r inner join aptg_guides as g on g.guide_uid = r.uid left join guides_biography as b on b.guide_id = r.uid left join guides_interests as i on i.guide_id = r.uid left join guides_tours as t on t.guide_id = r.uid left join guides_walks as w on w.guide_id = r.uid left join guides_lectures as l on l.guide_id = r.uid where show_on_web = '1' and (b.biography like '%london%' or i.interests like '%london%' or t.tours like '%london%' or w.walks like '%london%' or l.lectures like '%london%') In fact, this is a preliminary query to establish the total. If there is a total, I then run this: -- select distinct uid, firstname, lastname, year_qualified, other_qualifications, guide_driverguide, guide_photo_1 from aptg_guides_restricted as r, aptg_guides as g left join guides_biography as b on b.guide_id = r.uid left join guides_interests as i on i.guide_id = r.uid left join guides_tours as t on t.guide_id = r.uid left join guides_walks as w on w.guide_id = r.uid left join guides_lectures as l on l.guide_id = r.uid where g.guide_uid = r.uid and show_on_web = '1' and (b.biography like '%london%' or i.interests like '%london%' or t.tours like '%london%' or w.walks like '%london%' or l.lectures like '%london%') order by from_unixtime(unix_timestamp(guide_last_updated)) * (rand(1569933185) + ((length(guide_photo_1) 1) / 3)) desc -- ...and if I run that directly in the SQL window in phpMyAdmin, I get the same error: Unknown column 'r.uid' in 'on clause'. ¿Qué? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multi Threaded on RedHat ES 4
Ed Pauley II wrote: I recently changed from SuSE ES 8 to RedHat ES 4 running MySQL 4.0.26 (I will be upgrading to at least 4.1 after busy season). I compiled my own version this time and when I start the server I see only one mysqld process. I am used to seeing hundreds as my max connect is set to 1000. This is for a very busy website. Did I miss a compile option? Is this an OS thing? I am putting this thing live and we have a couple of very busy weekends coming up. Any help would be appreciated. Thanks in adv! Ed man ps now look at the 'H' not '-H' option. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select records not in a *particular* many-to-many relationship
I've checked the archives and found an explanation as to how the check if a record is not in a many-to-many table. The answer to that is somewhat simple and clear to me. But here's my problem: how do you check if a record doesn't have a *particular* many-to-many relationship? As in, let's say I have three tables: users, groups, and users_groups linking the two in a many-to-many relationship. Now let's say that I want to select all users who are not in the group Group1 -- that is, that user may have entries in the users_groups table, but they would be for other groups, not Group1. One more thing: this is easily done with subqueries, but for performance reasons, I need to do it with explicit joins. Anyone know how I can do this? SELECT u.userID FROM users u LEFT JOIN user_groups ug ON u.userID = ug.userID and ug.groupID = 'Group1' WHERE ug.groupID IS NULL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySql Error Number 1130
Duzenbury, Rich wrote: Wow, I found the problem! I think this may be a bug. In my case, I've got three instances running on ports 3306, 3307, and 3320. On the local machine, I connect to them via mysql -p --port-3306 --host=localhost mysql -p --port-3307 --host=localhost mysql -p --port-3320 --host=localhost Except that connecting to port 3307 doesn't really happen. It seems that the command line client connects to the main instance via the default socket when the host is not specified, or is the value localhost. So, even though I've specified the host and port, I wind up connecting to the main instance. I found this out by stopping the main instance on 3306, and then I issue: LX09:/etc # mysql --port=3307 ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib /mysql/mysql.sock' (2) LX09:/etc # mysql --port=3307 --host=localhost ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib /mysql/mysql.sock' (2) localhost means socket. LX09:/etc # mysql --port=3307 --host=127.0.0.1 -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. LX09:/etc # mysql --port=3307 --host=nnn.nnn.nnn.nnn -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Note that /var/lib/mysql/mysql.sock is the socket associated with the main instance, not the alternates. It seems that if I *specify* a port, then mysql ought to use that port. It only seems to be an issue when not specifying a --host, or when using the value 'localhost'. It's a bit terrifying because during all my testing, I am thinking I am connected to the correct instance, when in fact, I was not. It will be very easy to blow away the main instance data by mistake. Can I somehow convince the mysql command line client to use the specified parms, rather than the (incorrect) socket All of the above is the correct and documented behavior. This is covered in the manual, and can be found many times in the archives. Thank you. Regards, Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select a value between dates.
George Law wrote:
This brings up a question I was asked...
Which is more efficient?
Select . where date between '-mm-dd hh:mm:ss' and '-mm-dd
hh:mm:ss'
This one.
Or
Select . where unix_timestamp(date) between
unix_timestamp('-mm-dd hh:mm:ss') and unix_timestamp('-mm-dd
hh:mm:ss')
This one cannot use an index.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is port forwarded connection taken as local?
Bing Du wrote: Hello everyone, What I'm after is trying to figure out a way to centrally and remotely managing (e.g. on server1) our MySQL servers (server2 is an example) on different machines. Right now, these MySQL servers are all set up to only accept logons from localhost. My questions: 1. server1 has to be able to connect to server2 directly via SSH, right? 2. On server2, does 'grant ...to [EMAIL PROTECTED] identified by ...' have to be done? I've been looking through the relevent threads regarding how to make port forwarding for MySQL work. I saw one thread mentioned that port forwarded MySQL connection was taken as a local connection. But my own testing cannot agree with that. I'm confused. Here is what I did: On server1: server1% ssh -2 -l myusername -N -L 3307:server2:3306 server2 server1% mysql -P 3307 (in another term window) ERROR 1045 (28000): Access denied for user 'myusername'@'localhost' (using password: YES) You need to grant permissions to 'myusername'@'localhost' as indicated in the line above. But on server2, the following command works fine. server2% mysql -h localhost -u myusername -p I'd appreciate if anybody would shed some light. Bing -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RIGHT JOIN better than INNER JOIN?
Robert DiFalco wrote: For me the argument is a little pedantic. The contract of the descriptor table is that it must reference a name; there is code and constraints to enforce this. I am happy to have the query return nulls to indicate a programming error that can be quickly addressed. _If_ (after buffer tuning et al) a RIGHT JOIN still provides a substantial performance improvement over a FULL JOIN in this case, my customers would want me to provide that rather than have me tell them it is an inappropriate join or that I am asking the database server developers to improve their query optimizer. I wasn't really looking to get into a philosophical debate on correctness so let me restate my question a little better. Is there a reason in MySQL/InnoDB why a RIGHT JOIN would substantially out perform a FULL JOIN in those cases where the results would be identical? It is a little difficult to test query performance empirically since performance will change as different indices are swapped in and out of memory buffers and such (although I have turned query caching off), but it appears that for a table with 1-2 million rows a query similar to what I posted here was faster with a RIGHT JOIN. You have not given enough information to even make a guess. Show the create tables for each table, and the output of explain for each query to see what keys are being used. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: still cannot start MySQL
Jon Miller wrote: I'm still having a problem starting MySQL. I get the following message: Unable to initialise database connection: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' I've check the /etc/mysql/my.cnf and the sock file is supposed to load in '/var/run/mysqld. Thanks Start the server. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Some queries use 100% CPU after restore
[EMAIL PROTECTED] wrote: I'm moving a database to a new server. I'm using MySQL v5.0.16 on 'Windows. I used the MySQL Administrator to backup on the old system and restore on the new one. Everything is fine _except_ on the new server, some queries take 2-3 minutes with MySQL using 100% of the CPU. I've dropped unneeded views, done maintenance, verfied that all the indices I expect are there, etc. What's very, very strange is that it seems that the long queries are for old records and records created since the move are OK. I realize this is a vague request but I've been poking at this for a long time without getting any good clues or making any real headway. Any kind of brainstorming on things to check would be very welcome. Thanks. Chris Analyze tables. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What is the proper (least expensive) way to do this
RedRed!com IT Department wrote: So, for my peace of mind, I would need to follow these procedures to ensure Martijn's issue doesn't happen: 1. lock the table 2. execute my select 3. insert if it does not already exist 4. unlock the table On a high level, is this a correct list of procedures? Sean I would just do the insert, and check for a duplicate key error. If I get the duplicate key error, then update the record if necessary. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Very large from
fbsd_user wrote: Thank you Gabriel, more background information may result in a better concept, so here it is. The web based application I am writing deals with people who have an a home for rent. They can sign up for small fee and enter their rental property into the application so its searchable and viewable by the internet public. I have a membership DB with single members table containing 20 columns which holds the users signup info. Members can list more than one rental property. Members has opportunity to enter some portion of the 140 columns of data to describe the rental property. Currently the real_estate DB contains a single property table of 140 columns. I have grouped the info in the table into 3 groups, mandatory info deals with the business of renting, second group deals with location, third group deals with house characteristics. All the 140 columns of data will be shown on the detail property screen. Fields that don't pertain to that individual property will show up as blank because the user did not enter data. All fields are 'varchar' to conserve on unused space. One single record per listed property has all the info retrievable by one read, but has undesirable effects entering all of it from single form. Making 3 tables would mean duplication of some common key fields to allow retrieving all the associated rows to combine the data for displaying. I would think since most of the activity will be people on the internet searching for rental property, performance would be better to retrieve all the info with a single read. This makes the 3 table idea a bad performer. Why? It has been suggested to use session control as a staging function where the forms store the entered data so a single insert of all the data can be made to the table. Sessions store their data in standard flat files in /tmp. Not in my system. I have a session table that has records that consist of: session id variable name value Flat files are not known for their access speed and performance, with 2 or 3 fields this is ok but 100+ fields and this may become a major performance bottleneck. My current thinking is to break the entering of the property info into 3 separate forms, the first with the mandatory info does a insert to create the row with the remaining 100 columns being seeded with $t=''. Group 2 3 will them be a update to the row using the 'set' keyword just touching the columns under their separate control. Does this sound like a workable solution or is there some other approach that is better suited to my application? Thanks to all the people who have replied all ready. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: users browser caching the screen
fbsd_user wrote: Now I know what I am going to talk about is not directly related to this mysql list, but I am in need of some concept ideas. To set the background. It’s a very common practice in the registration process of a new user to verify the users email address is valid by sending a email to the entered email address with a link in it to a screen that updates the users emailed verified flag in his table record. I have such a process. Once a week I review my apache activity log and I noticed a lot of log records for the file that process the link to update the users email verified flag, (over 1500 from same ip address). To me this looked like an attack to break into my web application. Research and testing indicates that the screen is Cached by the users browser and he is changing the passed link info repeatedly in effort to break in. This screen is the only one that does not have session security control because it’s launched from the verify email I sent him. Now my registration sign up screen has a Captcha Security Code Random-Noisy-Image and part of that is a string of headers to the browser to stop caching. They look like this. // send several headers to make sure the image is not cached // taken directly from the PHP Manual // Date in the past header(Expires: Mon, 26 Jul 1997 05:00:00 GMT); // always modified header(Last-Modified: . gmdate(D, d M Y H:i:s) . GMT); // HTTP/1.1 header(Cache-Control: no-store, no-cache, must-revalidate); header(Cache-Control: post-check=0, pre-check=0, false); // HTTP/1.0 header(Pragma: no-cache); Now my desire is to somehow have the screen that is the target of the email link to issue these headers before displaying its html code so the user can not any longer run a script against this screen trying to break in. Is this possible and if so how. Is this kind of attack happening to anyone else? Thanks for any help you can offer. You have no way of knowing it the user is running a browser at all. He could have grabbed the page once, and run a script that pretends to be a browser. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help creating index's for this query...
Cory at SkyVantage wrote: I need help, I'm somewhat new to indexing, I am joining two tables and need to optimize the speed of the query. I'm running the NDB storage engine so the foreign key stuff is disabled. With that in mind, here's the query and additional comments below: SELECT COUNT(*) Count FROM pnr_passengers pax INNER JOIN pnr_status status ON pax.ID = status.ID_passengers INNER JOIN pnr_seg ments ps ON status.ID_segments = ps.ID WHERE status.res_status='0' AND ps.origin='SJU' AND ps.destination='SIG' AND ps.flight_ date='2006-03-10' AND ps.flight_number='218'; Field types pax.ID, bigint (keyfield, indexed) status.ID, bigint (keyfield, indexed) ps.ID, bigint (keyfield, indexed) status.res_status, int ps.origin, char(3) ps.destination, char(3) ps.flight_date, date ps.flight_number, int As you can see I'm joining three tables here each having a one-to-many relationship with each other pnr_passengers -one to many with- pnr_status pnr_status -one to many with- pnr_segments Here's an EXPLAIN with \G: *** 1. row *** id: 1 select_type: SIMPLE table: status type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 100 Extra: Using where *** 2. row *** id: 1 select_type: SIMPLE table: pax type: ref possible_keys: PRIMARY key: PRIMARY key_len: 8 ref: W4_AirlineData.status.ID_passengers rows: 1 Extra: *** 3. row *** id: 1 select_type: SIMPLE table: ps type: ref possible_keys: PRIMARY key: PRIMARY key_len: 8 ref: W4_AirlineData.status.ID_segments rows: 1 Extra: Using where Any help you anyone can provide as to the best way to create these index's that'd be great! This is an OFTEN used query in our software and the tables with have many rows. Cory You are using indicies here, so I don't understand what you are asking. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help creating index's for this query...
Cory at SkyVantage wrote: gerald_clark wrote: Cory at SkyVantage wrote: *** 1. row *** id: 1 select_type: SIMPLE table: status type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 100 Extra: Using where using indicies here, so I don't understand what you are asking. There are no indexes used on the above. Right? or am I missing something? (the joins are using the pk index's, but the WHERE part of the statement isn't, because there are no index's on those fields.) Cory How many possible values may res_status have? What percentage for each value? It may not be possible to use an index on this field. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help creating index's for this query...
Cory at SkyVantage wrote: gerald_clark wrote: How many possible values may res_status have? What percentage for each value? It may not be possible to use an index on this field. The res_status field may have up to 10 different values from 0-9. and there will be a huge amounts of '0' and thousands of records with 1-9 (most likely ~70% '0's) Cory Then a full table scan is faster than using an index for res_status=0. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: TimeStamp issue
rtroiana wrote: Hi All, I have recently noticed in the MySQL 5.0 documentation in section 11.3.1. The DATETIME, DATE, and TIMESTAMP Types, it's mentioned that TIMESTAMP values cannot be earlier than 1970 or later than 2037. This means that a date such as '1968-01-01', while legal as a DATETIME or DATE value, is not valid as a TIMESTAMP value and is converted to 0. Is that a correct range for TimeStamp? It's not big enough to be used in a real life application. Why not? It is not 2037 yet. Timestamp is designed to record when records are updated, not for storing arbitrary dates and times. I plan to use DATETIME instead of TIMESTAMP. I used to use CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP as default value for my TimeStamp column. Is there a way to assign default value to a DateTime column, since I couldn't find that in the documentation? Thanks, Reema -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM Backup
Jeff wrote: Currently I backup my MyISAM tables every night by running a shell script that does the following: Run: Mysqlanalyze, mysqlrepair, mysqloptimize on all the tables Then shutdown mysql Then tar all the .MYI, .MYD and .frm files from the database's directory to a backup director Start MySQL again. Later on an outside backup device connects and backs up the tar file for archiving. Question: Is it safe to do this without actually shutting down the mysql db? Perhaps putting a write lock on all the tables first so that they can be read but not written to during the tar. Does it matter if I'm tar'ing a file while mysql has it open? Thanks, Jeff Look at mysqlhotcopy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Alter table syntax question -
bobgoodwin wrote: I am running MySql 4.1.6 in FC-4 Linux amd it is a huge learning exercise for me! I am using O'Reilly's MYSQL Cookbook, have gone through numerous pages of the reference manual and stuff on Google but still can get the proper commands and syntax to alter the following table T1. I would like Createdate to show the date the record was crated and remain that. I would like Workdate to show the current date unless changed by the user and remain there until the next time there was a change in the record. It would be good if these dates would come up immediately without re-opening the table as it seems to do now after my best efforts [which have never been completely successful] although that may well be a problem with Navicat which I am using as a GUI? My efforts at setting up the table have all been via the mysql command line. If someone could provide an example of a working ALTER TABLE T1 . command it would be greatly appreciated. Thank you. Bob Goodwin Zuni, Virginia # uname -a Linux box3 2.6.11-1.1369_FC4 #1 Thu Jun 2 22:55:56 EDT 2005 i686 athlon i386 GNU/Linux mysql select version(); +---+ | version() | +---+ | 4.1.16| +---+ 1 row in set (0.00 sec) mysql describe T1; ++--+--+-+-+ + | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+ + | ID | int(6) | | PRI | NULL| auto_increment | | Createdate | timestamp| YES | | -00-00 00:00:00 | | | Lastname | varchar(75) | YES | | NULL | | | Firstname | varchar(75) | YES | | NULL | | | Address| varchar(75) | YES | | NULL | | | City | varchar(30) | YES | | NULL | | | Zip| varchar(15) | YES | | NULL | | | Area | varchar(20) | YES | | NULL | | | Tel01 | varchar(15) | YES | | NULL | | | Tel02 | varchar(15) | YES | | NULL | | | Tel03 | varchar(15) | YES | | NULL | | | Tel04 | varchar(15) | YES | | NULL | | | Category | varchar(20) | YES | | NULL | | | Workdate | timestamp| YES | | -00-00 00:00:00 | | | Notes | varchar(255) | YES | | NULL | | ++--+--+-+-+ + 15 rows in set (0.01 sec) You have Createdate and Workdate swapped. The first timestamp gets updated on every update to the record. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with a join query
Yoed Anis wrote: Hi all, I'm trying to do the following. I have three table: Table a has address information: address_id | City | State | Zip 1Austin TX 78758 2 Dallas TX 77000 3 Galveston TX 77550 Table b has information about the location: address_id | Location_id | Location_name 11The Place 12The Place Before 23A shop Table c has montlhy sales history Locationid | MonthYear | Sales 12005-01-01 299 12005-02-01100 12005-10-01300 22005-01-01 154 32005-10-1099 Not every location has sales information. I am trying to create a query where I can SELECT the Locationname, City, State, Zip, and the SUM(sales) if the place has sales. So far, despite playing around with joins for more hours than one should ever dedicated to the matter, I haven't been able to include SUM(sales) without excluding listings without sales. So far this is my best shot: SELECT locationname, city, state, zip, SUM(sales) as 'Sales' FROM a, b LEFT JOIN c ON (b.locationid = c.locationid) WHERE a.address_id = b.address_id AND monthyear 2005-01-01 GROUP BY c.locationid SELECT locationname, city, state, zip, SUM(sales) as 'Sales' FROM a INNER JOIN b ON a.address_id = b.address_id LEFT JOIN c ON b.locationid = c.locationid and monthyear2005-01-10 GROUP BY c.locationid This however, will return only records with Sales and not those without it. I haven't been able to force adding empty rows from table c... Doing AND c.locationid IS NULL returns no results at all. Any help would GREATLY be appreciated!!! Thank you!! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: JOINs with result of aggregate function fails with error #1054
Guillaume Boissiere wrote: This must have been asked before but I could not find the answer searching the list archives. I have a simple table: CREATE TABLE `license` ( `id` int(11) NOT NULL auto_increment, `firstname` varchar(100) NOT NULL default '', `lastname` varchar(100) NOT NULL default '', `host_address` varchar(100) NOT NULL default '', `is_visible` tinyint(4) NOT NULL default '1', PRIMARY KEY (`id`) ) TYPE=MyISAM; This first query works (MySQL 4.0.25): SELECT l.id, MAX(l.id) AS maxid, l.host_address FROM `license` l WHERE l.is_visible='1' GROUP BY l.host_address ORDER BY maxid DESC but this second query fails with the error: #1054 - Unknown column 'maxid' in 'on clause' SELECT l.id, MAX(l.id) AS maxid, l.host_address, l2.firstname, l2.lastname FROM `license` l INNER JOIN license l2 ON l2.id=maxid WHERE l.is_visible='1' GROUP BY l.host_address ORDER BY maxid DESC Is there a way to do a join with the result of an aggregate function (in this case MAX(id)) in one query, or do I have to use multiple queries for this? Thanks in advance! Guillaume You cannot join on an aggregate function. The value of maxid cannot be determined until after the join is completed. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Byte Swapping (Re Post)
[EMAIL PROTECTED] wrote: David Godsey [EMAIL PROTECTED] wrote on 02/14/2006 03:28:41 PM: Well, just thought I'd try one more time because I didn't get an answer to my question last time. So what I have is a random data stream that is sent in raw form, and based on some data definition, I can assemble with the correct data types and such. One of my requirements is that I have to store the data in raw form, and when I pull the data out, it displays based on the configuration (with the correct data types and such). So floats and doubles are IEEE standards so I don't have to worry about those, however with integer types, I may need to do some byte swapping (because this data can come from variouse systems that could be either big or little endian). So I am singling out the data I need, but now I need to add the ability to byte swap the data. Keep in mind that it would be best if I can do this in SQL so that it is portable. I realize that it can easily be done in C, but that makes my code less portable (which is also a requirement, to have it portable that is). So does anybody know of a MySQL function that is already implemented to do byte swapping? or know of a way to implement this in SQL? If not, is my only other option to write a UDF? Thanks for any help. Accomplishing the impossible means only that the boss will add it to your regular duties. David Godsey Native functions? No. Something you can cobble together? Yes. There should be several ways you can deal with your data as a string of binary characters. Just re-sequence those and you should have your bytes swapped. One idea is to use the substring functions directly on your BINARY string. Another is to use the substring functions in combination with HEX()/UNHEX() to work on an escaped version of your BINARY string. Would not the first zero value character terminate the substring, rendering it invalid? Sorry or the lame ideas but usually things like this are not handled at the database layer but rather in the application layer. Depending on which version of MySQL you are using you may be able to define a FUNCTION (a different creature than a UDF) or a STORED PROCEDURE to do the swapping. Both will be pure SQL and should meet your compatibility needs. Neither will be as fast as creating and registering a UDF, though. Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: (Windows ODBC/MySQL Debug) EMERGENCY HELP NEEDED
Michael Joyner wrote: (OT) (Windows ODBC/MySQL Debug) EMERGENCY HELP NEEDED -- The Scenario: ~~ We out locked out of our door lock database. The database is Sybase Sql Anywhere version 7.0 The software uses a hard coded DBA password that is *not* the word 'sql' A Maybe Solution: ~~ The software does *not* do a check on it's ODBC DSN to see what type of driver is being used. I can successfully have the software use the MyODBC debug dll which, because it's password is unknown to the MySQL server fails with an authentication error. The debug version version of the shipped MyODBC plugin does not log the password. I need a copy of the MyODBC debug plugin which *WILL* log the password. *** PLEASE HELP US FIX OUR DOOR LOCK SYSTEM! *** ~ Michael Joyner System Administrator / 904-470-8170 Edward Waters College 1658 Kings Road Jacksonville, FL 32209 MyODBC does not connect to Sybase. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Server Shutdown, Start Restart
Joseph E. Maxwell wrote: FreeBSD / UNIX platform, MySQL ver.4.0.16 Prob. of growing /tmp file, solved by introducing a cron job to clean up the folder intermittently. Prob.solve but new one created - mysql socket wiped out. Could not restart the server with all the standard methods. Complained of other running processes. Killed the orphaned processes runining. Aparently not. kill any process containing 'mysql' in the process name and restart the server. Alternatively: re-boot the server. But still can't restart. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Help
Ian Barnes wrote: Hi, This is my current query which works in mysql 4, but not in 5. Its from mambo, but im trying to modify it because they don't officially support mysql5 yet. The original query: SELECT c.*, g.name AS groupname, cc.name, u.name AS editor, f.content_id AS frontpage, s.title AS section_name, v.name AS author FROM mos_content AS c, mos_categories AS cc, mos_sections AS s LEFT JOIN mos_groups AS g ON g.id = c.access LEFT JOIN mos_users AS u ON u.id = c.checked_out LEFT JOIN mos_users AS v ON v.id = c.created_by LEFT JOIN mos_content_frontpage AS f ON f.content_id = c.id WHERE c.state = 0 AND c.catid=cc.id AND cc.section=s.id AND s.scope='content' AND c.sectionid='1' ORDER BY cc.ordering, cc.title, c.ordering LIMIT 0,10 My modified version: SELECT c.*, g.name AS groupname, cc.name, u.name AS editor, f.content_id AS frontpage, s.title AS section_name, v.name AS author FROM mos_content c, mos_categories cc, mos_sections s INNER JOIN mos_groups g ON (g.id = c.access) LEFT JOIN mos_users u ON (u.id = c.checked_out) LEFT JOIN mos_users v ON (v.id = c.created_by) LEFT JOIN mos_content_frontpage f ON (f.content_id = c.id) WHERE c.state = 0 AND c.catid=cc.id AND cc.section=s.id AND s.scope='content' AND c.sectionid='1' ORDER BY cc.ordering, cc.title, c.ordering LIMIT 0,10; The error I get for both ones is: Unknown column 'c.access' in 'on clause' Thanks and sorry for the stupid question. Cheers Ian Replace all your comma joins to INNER JOIN syntax -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unable to initialise database
Jon Miller wrote:
Having a problem connecting to a MySQL database from a mail server. In the
mail logs I'm getting:
mail MailScanner[4904]: Unable to initialise database connection: Can't connect
to MySQL server on '192.168.10.4' (110)
In the script that is running it has a mysql_connect statement with the correct credentials. From the command line of the mail server I can issue the command
# MySQL -u mailwatch -h 192.168.10.4 mailscanner -p
then I give it the password after which I have a MySQL prompt.
I have in the database the user name with GRANT privileges on both local host
and the mail server.
So I'm trying to figure out why it does not work from within the script. I
think it may have to do with the DBI connector.
Tried the following but getting errors
#!/usr/bin/perl
use DBI
You forgot a semi-colon after 'use DBI'.
$host = '192.168.10.4';
$port = '3306';
$dbh = DBI-connect (DBI:mysql:mailscanner; mailwatch, mailwatch);
if ($dbh) {print Connection successful! \n }
error message:
DBI version 192.168.10.4 required--this is only version 1.46 at
/usr/share/perl/5.8/Exporter/Heavy.pm line 107.
BEGIN failed--compilation aborted at ./dbi.pl line 5.
Any ideas?
Jon
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump
Giandomenico Sica wrote: Hi, I've a little problem with mysqldump. I'm using wordpress in localhost. Now I'd like to put online my wordpress website, including the related mysql database. In order to make this operation, I've used the command: mysqldump database name This will dump to the screen. If you want it to be dumped to a file you have to redirect output to a file. Ex: mysqldump database name database.name.sql The work seems correct, but the problem is that I'm not able to find the exported sql file in my computer (also by using the find file option). I use Linux Ubuntu. Can you help me please? Furthermore, how should I do in order to load online the exported database? Many thanks in advance for the answer. Cheers, nic -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DATE field key depends on value?
sheeri kritzer wrote:
Hi folks,
I'm attempting to optimize a query -- it's quite a simple one, actually.
SELECT uid from Bill_Sales WHERE startDate '[some date]';
mysql show create table Bill_Sales\G
*** 1. row ***
Table: Bill_Sales
Create Table: CREATE TABLE `Bill_Sales` (
`sales_id` int(4) unsigned NOT NULL auto_increment,
`uid` int(10) unsigned NOT NULL default '0',
`created` datetime NOT NULL default '-00-00 00:00:00',
`modified` timestamp NOT NULL default '-00-00 00:00:00',
`startDate` date NOT NULL default '-00-00',
`endDate` date NOT NULL default '-00-00',
`typesale` enum('pos','void','chargeback','refunded') default NULL,
PRIMARY KEY (`sales_id`),
KEY `uid` (`uid`),
KEY `startDate` (`startDate`,`endDate`,`typesale`),
KEY `endDate` (`endDate`,`startDate`,`typesale`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql explain SELECT uid from Bill_Sales WHERE startDate '2005-11-22';
++-++--+---+--+-+--+-+-+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows| Extra |
++-++--+---+--+-+--+-+-+
| 1 | SIMPLE | Bill_Sales | ALL | startDate | NULL |
NULL | NULL | 1028766 | Using where |
++-++--+---+--+-+--+-+-+
1 row in set (0.00 sec)
mysql explain SELECT uid from Bill_Sales WHERE startDate '2005-11-23';
++-++---+---+---+-+--++-+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra |
++-++---+---+---+-+--++-+
| 1 | SIMPLE | Bill_Sales | range | startDate | startDate |
3 | NULL | 192022 | Using where |
++-++---+---+---+-+--++-+
1 row in set (0.00 sec)
The cutoff date for using the index versus not using the index is
around 2 months ago!
This always happens on the production server, but I cannot get it to
work if I recreate the table without all the data. However, we've
replicated the data to a few machines, and the explains are consistent
with the replicated data.
So I run a REPAIR TABLE, which should fix the indexes. It definitely
changed something, because now the cutoff date is about a week ago.
mysql explain SELECT uid from Bill_Sales WHERE startDate 2006-01-13;
++-++--+---+--+-+--+-+-+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows| Extra |
++-++--+---+--+-+--+-+-+
| 1 | SIMPLE | Bill_Sales | ALL | startDate | NULL |
NULL | NULL | 1028777 | Using where |
++-++--+---+--+-+--+-+-+
1 row in set (0.00 sec)
mysql explain SELECT uid from Bill_Sales WHERE startDate 2006-01-14;
++-++---+---+---+-+--++-+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra |
++-++---+---+---+-+--++-+
| 1 | SIMPLE | Bill_Sales | range | startDate | startDate |
3 | NULL | 190891 | Using where |
++-++---+---+---+-+--++-+
1 row in set (0.00 sec)
Why would the query do this? And why does it change when I run a repair table?
(show status:
mysql show status;
+++
| Variable_name | Value |
+++
| Aborted_clients| 74279 |
| Aborted_connects | 146|
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 1163526992 |
| Bytes_sent | 359522512 |
| Com_admin_commands | 0 |
| Com_alter_db | 0 |
| Com_alter_table| 45 |
| Com_analyze| 1 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 154039613 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function
Re: Error from mysqldump
Rhino wrote:
I have an automated backup script that has been running daily for a
couple of years now. It has never given me trouble until the last two
days. For the last two days, I have been getting this message when
backing up my newest database:
/usr/bin/mysqldump: Got 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 'References READ /*!32311 LOCAL */'
at line 1 when using LOCK TABLES
This is the relevant portion of my backup script:
for ONE_DBNAME in `echo show databases | mysql -s -u $USERID -p$PASSWORD`
do
echo
echo Backing up database $ONE_DBNAME;
/usr/bin/mysqldump --opt --verbose -u${USERID} -p${PASSWORD}
${ONE_DBNAME} -r
${BACKUP_PATH}/${ONE_DBNAME}.${BACKUP_TIMESTAMP}.sql
I would look here. This is a dangerous expansion. A space or ';' in any
of these variables my generate unwanted commands.
Use quotes around the argument to -r.
Try.
/usr/bin/mysqldump --opt --verbose -u${USERID} -p${PASSWORD}
${ONE_DBNAME} -r ${BACKUP_PATH}/${ONE_DBNAME}.${BACKUP_TIMESTAMP}.sql
or
/usr/bin/mysqldump --opt --verbose -u${USERID} -p${PASSWORD}
${ONE_DBNAME} ${BACKUP_PATH}/${ONE_DBNAME}.${BACKUP_TIMESTAMP}.sql
echo Deleting these old backups for this database...
/usr/bin/find ${BACKUP_PATH} -mtime
+$NUMBER_OF_DAILY_BACKUPS_TO_KEEP -name $ONE_DBNAME'*' -print;
#display old backups (if any)
/usr/bin/find ${BACKUP_PATH} -mtime
+$NUMBER_OF_DAILY_BACKUPS_TO_KEEP -name $ONE_DBNAME'*' -exec rm '{}'
';' #delete old backups (if any)
done
I'm at a loss to see why I'm getting this error for only one database
when the exact same logic is applied for each of my databases and
works fine for all the others.
I've tried doing the backup manually from the command line and found
that I got the same error when I tried to backup the Maximal database
that way; a manual backup of another database worked fine.
The only idea I have that seems vaguely plausible is that there is
something internally wrong with my database but I'm darned if I know
what the problem could be. When I do 'select *' against each of the
five small tables in this database, each returns exactly the right
data and there are no errors or warnings of any kind.
Can anyone suggest queries or commands that would reveal the status of
my database and its tables to make sure something is not messed up?
Any suggestions on resolving this problem would be greatly appreciated.
---
Rhino
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select records added in last hour
Russell Horn wrote: I have a table containing a timestamp field, `insert_time` The manual is clear when it comes to selecting records from the past week or month, I can use: SELECT COUNT(*) FROM statistics WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) = `insert_time`; What I am less sure about is how I would select records that had been inserted to thee table in the previous hour. I understand the date functions will ignore the hh:mm:ss - will the time functions ignore the date? Can anyone suggest what function I need for this select? Have you tried any of the functions in the manual? select NOW(); select DATE_SUB(NOW(),INTERVAL 60 MINUTE); select DATE_SUB(NOW(),INTERVAL 60 MINUTE)+0; select DATE_SUB(NOW(),INTERVAL 1 HOUR); select DATE_SUB(NOW(),INTERVAL 60 HOUR)+0; Thanks, Russell. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help in joining three tables
Please do not hijack someone elses thread. ]Imran wrote: Hello All: I need to join three tables but I am not sure how to structure the query. I need to join table1 to table2 and then join table3 to this result set. So like (table1 join table2) join table3. Table1 and Table2 will be joined on ProdNo,CustNo and Branch. Table3 will be joined to the result set by CustNo and Branch. Best regards Imran. Go ahead. That should work if you leave out the parenthesis. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 5.1 Delopment source
Beau E. Cox wrote: Hi - I am trying to download the 5.1 development sources as per the documentation; when I try this: export PATH=/home/beau/src/bitkeeper/bk_client-1.1:$PATH sfioball -r+ bk://mysql.bkbits.net/mysql-5.1 mysql-5.1 I get this: ERROR-cannot cd to mysql-5.1 (illegal, nonexistant, or not package root) I think the free bk_client is setup as per the documentation; maybe I just don't understand the mysql source tree structure. There is no free bitkeeper client. Free Bitkeeper was shutdown months ago. Does anyone see what I'm doing wrong? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: an backup syntax error
wangxu wrote: Follow is my preform and result: - mysql backup table ht_detail to '/'; +++--+--+ | Table | Op | Msg_type | Msg_text | +++--+--+ | test.ht_detail | backup | error| Failed copying .frm file (errno: 13) | | test.ht_detail | backup | status | Operation failed | +++--+--+ 2 rows in set, 1 warning (0.00 sec) - How to solve it? I use 5.0.16. Normal users cannot write to the root directory. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump
Aaron Morris wrote: I have a very simple database I am trying to backup. I run myslqdump and it gives me the code below. That is not code. It is a file of sql statements. Which does nothing but cause errors when I try to run it. You can't run it. It is input for the 'mysql' client program. mysql thedumpfile. Thank you in advance for your help -Aaron -- MySQL Administrator dump 1.4 -- -- -- -- Server version 4.1.11-nt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump
Aaron Morris wrote: Right, that is what I am doing, but it does not work. Since you don't tell us what you did, what error messages you got, or what 'does not work' means, little can be done to assist you. Have you used mysqldump successfully? - Original Message - From: gerald_clark [EMAIL PROTECTED] To: Aaron Morris [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, November 15, 2005 1:40 PM Subject: Re: mysqldump Aaron Morris wrote: I have a very simple database I am trying to backup. I run myslqdump and it gives me the code below. That is not code. It is a file of sql statements. Which does nothing but cause errors when I try to run it. You can't run it. It is input for the 'mysql' client program. mysql thedumpfile. Thank you in advance for your help -Aaron -- MySQL Administrator dump 1.4 -- -- -- -- Server version 4.1.11-nt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why wont this work?
Ben wrote:
hey,
Could you tell me someone please why this simple setup wont work!
I am using SuSE9.3 and trying to set this up:
$ mysql -uroot -einsert into user(Host,User,Password)
values('localhost','guestbook',password('guestbook') mysql -p
$ mysql -uroot -einsert into user (Host,User,Password)
values ('localhost','guestbook',password('guestbook') mysql -p
this is the error:
$ ERROR 1064 (42000) at line 1: 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
could someone please shed some light on this, Maybe the answer is so obvious I
cant see it?
Thankyou very much
Ben
:')
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Confusion Over Numeric Types
Shaun wrote: Hi, When I create a table using my ISP's web control panel and I create a column with a type of TINYINT it automatically creates a column with a value of TINYINT(4). After looking at the documentation on the MySQL site it is not exactly clear what this means: http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html Is TINYINT(4) the same as an INT(4)? Thanks for your advice. TINYINT(4) means a type that holds a value for -128 to 127 displayed with max (4) character positions. The part in parenthesis is the input/output display parameter, not the number of bytes of storage. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to use logrotate to manage binary logs
Kristen G. Thorson wrote: Hi all, This may be a simple solution that I'm just not seeing. logrotate has trouble with binlogs since the extension keeps changing, so instead of actually rotating logs out, I just get a dir filled with binlog.001.1 binlog.002.1 binlog.003.1 binlog.004.1 binlog.005.1 binlog.006.1 and on to inifinity. The manual says If you supply an extension in the log name (for example, |--log-bin=/|file_name.extension|/|), the extension is silently removed and ignored, so it appears I'm stuck. Does anyone have some pointer for me to set up auto-rotation? I'm somehow not finding much info on this. kgt Don't do that. You will break replication. Set up a cron job to flush logs instead. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SOCKET directory
Barbara Deaton wrote: Thank you for the link. I understand that I need a mysql.sock file, what I don't understand is that I used to get one when I installed MySQL. When I look through all my 4.0.x mysql directories I see a socket directory that contains a mysql.sock file. With my 4.1 install I do not see this file. Why do I no longer get a socket/mysql.sock with the install? Thanks again for your time. -Barb. It is created by the server when the server starts, and deleted when the server stops. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql/php date functions..
bruce wrote: hi... i've got a question/problem that i can't seem to figure out. it should be simple/straightforward. i'm creating a test tbl id int t1 timestamp when i do a 'select * from tbl' i get what looks like a default date/time format in the timestamp column. i do a, (from mysql) 'insert into tbl (id, t1) values (1, 33)' and it fails... 33 is not a valid date/time. Did you try : 'insert into tbl (id) values (1)' ? i do a, 'insert into tbl (id, t1) values (1, now())' and it works... after i do the 'now()', i see what also looks like a date/time format in the timestamp column. any ideas/comments/thoughts as to what might be going on... Yes, it is working. thanks... ps. my goal is to create a quick/test php app where i can use the php 'time()' functin and insert it into the mysql tbl!!! Why? Timestamps update themselves. You don't need to insert them. You might try reading the manual for a description of how timestamp works. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: quoting strings/ints/vars in mysql
bruce wrote: hi... i've been lloking over various open source apps to get a feel for how the apps store and manipulate information as it's stored in the mysql db... i see some apps that put 's around strings that get stored in the db, as well as some apps that appear to put 's around numeric data... i'm trying to determine what's the best/correct approach. if i have a char data, should i place data in the colume that's been enclosed by 's? similarly, if i have numeric/date information, should it be enclosed in 's?? i'm currently looking through the mysql docs... thoughts/comments/etc... thanks -bruce [EMAIL PROTECTED] 'This is a string' This is not. Number - 12345 String- '12345' String columns should get strings. If you forget the ' marks, mysql will think the string is a field name. Numeric columns should get numbers. If you put ' marks around numbers, MySql will convert it. String conversions in select statements will slow things down. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: HTML in MySQL?
Vladimir B. Tsarkov wrote: Hello! Be careful here Vladimir, the (\n) are not 'null' characters; but newline characters. Agree, I was wrong. And i would highly recommend *not* replacing them with br tags as you write them into the database. This is asking for trouble on so many levels. The database will cope with carriage returns and newlines just like any other character, so will have no problems. HTML is just string; treat it as such and don't give it anymore credit than that and you'll be fine. If you need to output a HTML string (not in a textarea field), you'll need to replace \n with br. That is why, I think that it is better to replace it before saving (You will not need to replace \n during the output process. It will save you some lines of code, and add productivity.). Of course, you need to be sure that your program will make HTML output more often, than any other type of output. But once you have done that, you can never recover the origional text. Try it on this email if you doubt it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Data import problems
Perhaps you mean 'sed'. Filipe Tomita wrote: tks for reply but i dont have acces to original database... the dump file not generated from me.. Someone tell me about SAD command to replace table name to correct name but i dont find anything about it. On 9/9/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Filipe Tomita [EMAIL PROTECTED] wrote on 09/09/2005 04:37:01 PM: Hi people, I having problems to import a dump generated via mysql dump in mysql 4.0.20slackware version to a mysql 4.1.14. The dump generated with other table name (Insert into temp, temp2 , temp3) in 20 sql large (420mb.) sql files. I tried LOAD DATA INFILE but data not inserted correctly, someone help-me?? tks. Tomita If you look at the output from mysqldump, you will realize that it is just a series of SQL statements. Execute them with your command line client just as you would any other SQL script file. mysql database -u username -p dumpfile.txt or use the source or . commands if you are already in the CLI mysqlsource dumpfile.txt Where you will run into problems is if you didn't specify a max_packet_length WHEN YOU MADE THE DUMP. That option tells mysqldump just how long the longest single statement can be. If you try to run a dump file that contains a statement that exceeds the max_packet_length setting on your server, it will fail silently (server gone away). If you failed to dump with a maximum set, your options are to increase the MAX_PACKET_LENGTH on the server to accept the longest single statement in your dump file or to edit your dump by hand and break your long statements into shorter ones or both. Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: database problem
dEeZAcK SweETtY wrote: __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: cannot create database Description: I have already set the path to c:\mysql\bin and would like to create a database. When i enter the command creta database feedback; it comes out this error 'create' is not recognized as an internal or external command, operable program or batch file How-To-Repeat: code/input/activities to reproduce the problem (multiple lines) Fix: how to correct or work around the problem, if known (multiple lines) Synopsis: Cannot create database Submitter-Id: [EMAIL PROTECTED] Originator: Diana Organization: Student from Multimedia Universuty MySQL support: licence Severity: non-critical Priority: medium | high Category: mysql client Class: sw-bug doc-bug change-request support Release: mysql-3.23.38 Exectutable: [mysqld, mysqld-shareware, mysqld-nt or mysqld-opt] Environment: machine description System:XP Compiler: VC++ 6.0 Architecture: i You are trying to run mysql client commands from the Windows/Dos command line. But: 1. Delete the spam. If it looks like spam why read it? 2. Do not send attachments. Many people do not read attachments. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Help
Jason Chan wrote: I am using mysql 4.0.25 with no subquery support : ( Jason Chan [EMAIL PROTECTED] ¦b¶l¥ó news:[EMAIL PROTECTED] ¤¤¼¶¼g... I have a student Table and a SubjectGrade table Create Table Student( StudentID INT NOT NULL, StudentName VARCHAR(30) ) Create Table SubjectGrade( StudentID INT NOT NULL, Subject VARCHAR(30) NOT NULL, Grade CHAR(1) ) let's say have following record in SubjectGrade 1MathsA 1PhysB 1ChemA 2MathsA 2ChemA 3BioC 3ChemA I want to find out students who have got A in both Maths and Chem How the SQL look like? SELECT StudentName FROM Student INNER JOIN SubjectGrade sg1 ON Student.StudentID=sg1.StudentID INNER JOIN SubjectGrade sg2 ON sg1.StudentID=sg2.StudentID WHERE sg1.Subject='Maths' AND sg1.Grade='A' AND sg2.Subject='Chem' AND sg2.Grade='A'; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Databases in a different location than the default
Tim Holmes wrote: [Tim Holmes] Gleb, et. al. As you suggested, I have checked out the log files and this is what I have found: 050713 11:00:09 mysqld started 050713 11:00:09 [Warning] Asked for 196608 thread stack, but got 126976 050713 11:00:09 [ERROR] Can't start server: Bind on TCP/IP port: Address already in use 050713 11:00:09 [ERROR] Do you already have another mysqld server running on port: 3306 ? 050713 11:00:09 [ERROR] Aborting 050713 11:00:09 [Note] /usr/sbin/mysqld: Shutdown complete 050713 11:00:09 mysqld ended This suggests to me a communications problem on either the database server, or the file server where the databases reside. It suggests to me that you already have mysqld running. I guess the next question is how do I check to see whats going on here. I tried telnet 192.168.0.5:3306 and got the following [EMAIL PROTECTED] log]# telnet 192.168.0.5:3306 192.168.0.5:3306/telnet: Name or service not known This may suggest that telnet is not installed, or it may indicate That is not how telnet works. This is how telnet works. telnet 192.168.0.5 3306 another problem Any suggestions are welcome TIM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using START SLAVE [SQL_THREAD] UNTIL syntax
David Ulevitch wrote: [ Note to list admin, you are blocking messages from gmail.com ] MySQL List, I have a large MySQL database (around 20 gigs in total, some tables =3gigs. All tables are MyISAM. We have replication setup to a number of slaves including one dedicated for backups. All are running some semi-recent version of 4.1. As we know: Slaves are fine for distributing SELECT load. Slaves are fine for failing over. Slaves are fine for making backups. Slaves do nothing to help after a destructive query like delete from important_table; This should never happen but developers aren't perfect and don't always test enough in their dev environment so I want a solution for this type of catastrophe. Here's what I've thought up. I wanted the lists thoughts before I code it up. google was of no help. Setup: PRE) Setup a slave and configure skip-slave-start in the my.cnf. Configure the slave with CHANGE MASTER command and bring it up to sync w/ master. STOP SLAVE. System is now ready. 1) start mysql (skip-slave-start) 2) start mysql-slave-keepalive script on local machine. mysql-slave-keepalive script would be (more or less, I'd have more checks/auditing): 1) lynx --source http://private.internal.server/operations/ replication-status [contents $master_log_file:$master_log_pos] 2a) if (values == previous_values || values == 'HALT'); do stop slave; page_operations_team; sleep 30 mins; done; 2b) otherwise... 3) issue 'START SLAVE UNTIL MASTER_LOG_FILE='$master_log_file', MASTER_LOG_POS=$master_log_pos. sleep 30; goto 1. In our internal admin system there would be an interface for pressing the big red button to HALT slaving which could be issued by any team member at any time. This would, hopefully, give anyone 30 minutes to ensure access to a currently running database that hasn't been destroyed by the nefarious query. Some of my questions: 1) What are the benefits to using relay_log_file and relay_log_pos instead of master_log_file and master_log_pos? that the slave binlogs would already exist locally? Perhaps that's good or bad? thoughts? 2) Has anyone done something like this? 3) If I made it robust and flexible would people be interested in it? 4) Is there a better way? I've tested this all by hand and it seems to work extremely well and be well suited to scripting... In fact, I was surprised I wasn't able to google anything about it. Thanks for all feedback! David Ulevitch Nightly backup and binlogs can take you to any point in the day. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using START SLAVE [SQL_THREAD] UNTIL syntax
David Ulevitch wrote: On Jul 12, 2005, at 6:15 AM, gerald_clark wrote: Nightly backup and binlogs can take you to any point in the day. I failed to make my entire point clear then. That's true. But when someone does the oh fsck, I think I just ran a catastrophic query on our production database we would now have a running system in place that hasn't yet processed that query that can be compared against the real thing within seconds or minutes to see what the damage is and/or just try to repair. When you restore a 20gig+ database from binlogs and try to find a point-in-time, it takes more than seconds/minutes. You have tried a restore right??? :) -davidu Yes, we do restores and run bin logs for our customers whenever necessary. You can have more than 1 binlog/day so you don't have to edit monster log files. How are you going 'repair' deleted data without a restore? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: my-medium vs my-huge cnf files
Stephane Savage wrote: Hi, I'm quite new to mysql and I have a question regarding the server configuration files. I have a server with 2 go of memory and I would like to know the reason why when I run a query (select * from Mytable) that returns 400k rows the my-medium configuration always runs the query in 30 sec and the my-huge configuration runs it in about 35 to 50 sec its never the same. Doesn't the my-huge suppose to be more efficient and faster when you have the memory to handle it? Or will I only see a difference running a test with multiple concurrent queries? Thanks in advance! Stephane You may be starving the operating system disk cache. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't open privilege tables/mysql.sock
Tim Johnson wrote: * Gleb Paharenko [EMAIL PROTECTED] [050711 07:17]: Hello Gleb: Check that you have correct permissions set on MySQL datadir. See: http://dev.mysql.com/doc/mysql/en/mysql-install-db.html I will add to the comments above. I had to set the owner and group to mysql as per my redhat setup. More problems tho: == When invoking mysql, I get the following error message: Can't connect to local MySQL server through socket at 'var/lib/run/mysql/mysql.sock' hmm! mysql.sock was created at var/lib/mysql. owner=mysql, permissions=777 So again, is there paths/permissions problems? Since this is where your my.cnf says to put it, it would be a problem if it was not there. If you are going to override the default location of the socket for the server, you will also need to add an entry for the client. [mysql] socket=/var/lib/mysql/mysql.sock The following is in /etc/my.cnf -- [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid - Thank you for your help so far. Further support greatly appeciated. :-) Further pointers to docs invited! Regards Tim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Indexing not working
Sajith A wrote: Thank you Clark for your time Try using straight joins to force MySQL to join from qb_test_result first. The order chosen by the optimizer has no use for keys from this table. I tried to force indexes.. but it didn't help Thank you Since the query you supplied cannot use an index, force index won't help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MAC OS X backup after crash
Alla-amin wrote: Yes, 10.1.5 runs mysqk version 3, it was actually running 3.23.51 and 10.3 runs 4.0.14 I think. Ok - the database in question have been backedup with files such as table1.frm.gz table1.MYD.gz table1.MYI.gz and so on.gz When I create a new database on the new server and copy these files to the the new database, mysql doesn't recognize them. You need to unzip them, and verify correct ownership and permissions. When I rename table1.frm to table1.frm and copy it to the new server database - mysql says that there is no data there. I haven't tried this on a 10.1.5 system yet - will do that asap. Alex Dehaini App Engineer NAS GLOBAL NETWORKS Mobile = +233-24-877231 Office = +233-217012800/2 Email = [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Indexing not working
Sajith A wrote: EXPLAIN SELECT qb_test_result.id resultId, qb_question.testId AS testId, qb_test.title testName, qb_question.marks, qb_test.passrate, qb_test_result.marks testMark, qb_test_result.percentage testPercentage, qb_test_result.startTime, qb_test_result.endTime, qb_test_result.status FROM qb_test_result, qb_test_result_details, qb_test, qb_question WHERE qb_test_result.id = qb_test_result_details.resultId AND qb_test_result.testId = qb_test.id AND qb_test.companyId =1 AND qb_test.author = '2' AND qb_test_result_details.questionId = qb_question.id +++---+--+-+---+--+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +++---+--+-+---+--+-+ | qb_test_result | ALL| PRIMARY,testStudent | NULL | NULL | NULL | 2494 | | | qb_test_result_details | ref| resultId | resultId | 4 | qb_test_result.id | 45 | | | qb_test| eq_ref | PRIMARY,companyAuthor | PRIMARY | 2 | qb_test_result.testId |1 | Using where | | qb_question| eq_ref | PRIMARY | PRIMARY | 4 | qb_test_result_details.questionId |1 | | +++---+--+-+---+--+-+ Thank you Sajith A Try using straight joins to force MySQL to join from qb_test_result first. The order chosen by the optimizer has no use for keys from this table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AES Encryption
mos wrote: At 04:32 AM 6/15/2005, you wrote: Hi, I think of using AES Encryption for some time now, because it seems to be the most secure encryption method in MySQL at this moment and table encryption of some sort is not possible. I really wish MySQL would support table wide encryption because more and more news reports are showing the lax security is causing big problems for companies. Look at the CitiGroup fiasco: http://www.tallahassee.com/mld/tallahassee/business/11886144.htm http://www.oregonlive.com/business/oregonian/index.ssf?/base/business/1118225190236830.xmlcoll=7 This is going to cost them millions of dollars in PR and possibly a few lawsuits. If the database had table wide encryption, the loss of the tapes wouldn't have made the news. Also stories of hard drives walking out of secure rooms or re-sold with data still on it, does not instill any confidence in current security protocols. IMHO, table wide encryption would solve a lot of these problems. Other databases have implemented table wide encryption, why not MySQL? If the government passes a law to force this data to be encrypted, MySQL would be at a definite disadvantage to those databases that have table wide encryption. Mike You can use an encrypted file system, and encrypt your dumps before archiving. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unable to install mysql
Yannick wrote: Hey guys, I am not able to install properly mysql. Please see below the technical details or the bug report. The installation goes well until I try to add the root user : [EMAIL PROTECTED]:/usr/bin mysqladmin -u root -h fujitsu password x [EMAIL PROTECTED]:/usr/bin mysqladmin -u root password x mysqladmin: connect to server at 'fujitsu' failed error: 'Host 'fujitsu.local' is not allowed to connect to this MySQL server' [EMAIL PROTECTED]:/usr/bin mysqladmin -u root -h 192.168.234.2 password xx mysqladmin: connect to server at '192.168.234.2' failed error: 'Host '192.168.234.2' is not allowed to connect to this MySQL server' [EMAIL PROTECTED]:/usr/bin Continuously, and I've tried lots of combinations, he is failing to connect to the server. Please give me so hints for me to be able to install it. Best regards Yannick [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help needed with complex Query
Philip Lawatsch wrote: Hi, I'm trying hard to figure out how to perform a special query in mysql 4.0. I have one table widgets which has a column widget-id (int) and one column number_of_parts (int). And then I have another table part_mapping which has one column widget-id (int) and one column part_id (int). part_id is unique throughout the part_mapping table. The idea is that every widget consists of several unique parts. Now I want to select all widgets which are complete, this means where SELECT COUNT(1) FROM `part_mapping` WHERE widget-id = ... equals the number_of_parts of widget-id in table widgets. What I could do is simply loop over table widgets and execute a select count for every wiget. This would result in a huge number if queries needed form my client which is something I'd like to avoid. I pretty much have no idea how I can do this without nested queries (and to be frank not even how to do it with them) so I'd really appreciate any help! kind regards Philip Try this: SELECT widgets.widget-id, number_of_parts, count(partid) AS cnt FROM widgets INNER JOIN part_mapping ON widgets.widget-id = part_mapping.widget-id GROUP BY widgets.widget-id, number_of_parts HAVING cnt = number_of_parts; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance problems through gateway
Celona, Paul - AES wrote: I am running mysql 4.0.18 on Windows 2003 server which also hosts my apache tomcat server. My applet makes a connection to the mysql database on the server as well as a socket connection to a service on the same server. In the lab with only a hub between the client and server, the application performs well and data is transferred quickly. In the deployed environment with a pair of gateways in between, socket performance is not affected much, but the application gui bogs down on the database queries. Performance is so slow that some simple GUI updates take up to 5-7 seconds with only a simple 1 table update occurring. Does anyone have experience with this and/or can provide some insight? If your applet is making connections on each page, you might be having reverse dns problems. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem when running mysql server
qin lei wrote: I have instal mysql in /usr/local/mysql. When I run the server. The message showed on the screen is as follows: [EMAIL PROTECTED] bin]# ./safe_mysqld [1] 5122 [EMAIL PROTECTED] bin]# Starting mysqld daemon with databases from /var/lib/mysql STOPPING server from pid file /var/run/mysqld/mysqld.pid 050526 18:05:14 mysqld ended I think the server is closed automatically. I check the mysqld.log. It says: 050526 17:46:09 mysqld started Warning: Ignoring user change to 'root' because the user was set to 'mysql' earlier on the command line 050526 17:46:09 InnoDB: Started 050526 17:46:09 /usr/local/mysql/bin/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 050526 17:46:09 Fatal error: Can't open privilege tables: Can't find file: './mysql/host.frm' (errno: 13) 050526 17:46:09 Aborting 050526 17:46:09 InnoDB: Starting shutdown... 050526 17:46:11 InnoDB: Shutdown completed 050526 17:46:11 /usr/local/mysql/bin/mysqld: Shutdown Complete 050526 17:46:11 mysqld ended What is the problem? Error 13 is a permissions problem. The user 'mysql' does not have permission to read the files. Make sure mysql owns the database directory and all its contents. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slow DELETE query
Matthew Simpson wrote: I use Mysql to store call detail records from telephone calls. I have around 20 voice switches that send the call detail records in real time using INSERT statements. I am having a problem where I need to delete junk call records that get generated [old call records, call records with no accountcode, etc.], but when I try to run the DELETE query, SQL grinds to a halt which causes my voice switches to halt because they can't run the INSERT queries. Is this because of table locking? An example delete query: DELETE from cdr WHERE accountcode='' Is there a way to make the DELETE query run at a lower priority and allow the INSERTs? DELETE from cdr WHERE accountcode='' LIMIT 10 wait, and run again until there is nothing more to delete. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to set trabnsaction isoloation in my.ini file
Adam Lipscombe wrote: I have MySql 4.1.12 installed on an XP box in C:\mysql. I have the settings below in my C;\MySql\my.ini file: I have the transaction-isolation set to READ-COMMITTED but the server seems to ignore this. When the server starts up the tx_isolation as reported by mysqladmin extended-status is REPEATABLE-READ. I can set it manually from the mysql command line and that works. The tx_isolation is then reported as READ-COMMITTED. Its almost as if the server is not reading the my.ini file. transaction-isolation = READ-OMMITTED Have you tried leaving out the spaces? transaction-isolation=READ-OMMITTED -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: stopped while creating index.
Tom wrote: Thank you, Partha. What I really meant was that the process of creating index was stopped and I couldn't figure out and search out the log meaning 'Warning: Enabling keys got errno 116, retrying'. So I am not sure I will run into such problem next time. perror 116 Error code 116: Stale NFS file handle You aren't trying to use NFS to store your tables are you? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: backup a database
Rafael Diaz Valdes wrote: Hi, I 'm using mysqldump to backup my database, I wrote in my backup.sh the following string: $MYSQL/bin/mysqldump --opt databasename -user=root --password=rootpassword backup/mysql/databasename.sql I include backup.sh in my cron weekly (/etc/cron.weekly) but when it is executed appear databasename.sql empty, else if I run backup.sh in a shell konsole it is executed ok, do you know why can not execute the string in the cron ?? my mysql version is : 4.0.17 regards rafae Do you have $MYSQL set? This really isn't a mysql question. It is a matter of elementary troubleshooting. Add to your script: echo $MYSQL/bin/mysqldump /tmp/junk and see what you have. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: order by confusion
Schalk Neethling wrote: Greetings! This might be a stupid question but here goes: I have a table that contains a column entitled current_pos. I want to search this table and then order the results by current_pos. Now I am running the following SQL query on the table: SELECT * FROM ab_leader_board WHERE sex = 'F' and cup = 'Kids' ORDER BY current_pos DESC; After running this the results are returned but as 2, 1, 0 , 0 etc. If I use: SELECT * FROM ab_leader_board WHERE sex = 'F' and cup = 'Kids' ORDER BY current_pos ASC; It returns 0, 1, 2 How do I go about getting this to return the results as 1,2,3,4 etc.? Any help would be appreciated. Thank you! Well, since the results are 0, 1, 2 you are not going to get 1,2,3,4. You are getting what you are asking for, and it appears to be what you want, so what is the problem? A bit more detail and a real example might help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM error 127
[EMAIL PROTECTED] wrote: Hi, We're in the process of benchmarking/evaluating MySQL(4.1.9) on Linux for our data warehouse. I have a group of tables (9/2004 through 2/2005) that average about 95 million rows (215 byte rows). Using PERL and piping to mysql LOAD DATA is the way we've been loading. That all worked as advertized when single threaded. Once all the data was loaded one of the tables had several indexes created. Again, no problem. Then we tried running myisampack against one of the tables. It worked against the table, however other tables somehow got corrupted. Running a query against the table produced the following error: MyISAM Engine returned error 127 Ok, so we ran myisamchk --quick --recover It ran successfully (or so we thought). Query the table for rows and the number comes back. Query the data get same error message. Next, we ran myisamchk --force --extended-check It ran successfully (or so we thought). Query the table for rows and the number comes back. Query the data get same error message. Then decide to truncate the table. Same error. Dropped the table. Same error. Recycled MySQL then dropped the table. It worked. Any ideas as to what may be going on here. George You did not state that you stopped the server before running myisamchk. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: java.lang.OutOfMemoryError
huanggaofeng wrote: (B (BI have too many databases,when i select it by client ,but it has error . (B (Bthe error message is : java.lang.OutOfMemoryError (B (Bhow i can to resolve it. (B (B (B (BYou might try a java list. (B (B (B-- (BMySQL General Mailing List (BFor list archives: http://lists.mysql.com/mysql (BTo unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: extract numeric value from a string.
Eamon Daly wrote: Easy enough. Get the numeric part via CONVERT, then get the rest of the string from the length of the numeric part, plus one: SELECT tag, @num := CONVERT(tag, SIGNED) AS num_part, SUBSTRING(tag, LENGTH(@num) + 1) AS rest_of_string from tags; ++--++ | tag| num_part | rest_of_string | ++--++ | 1foo |1 | foo| | 23bar | 23 | bar| | 234baz | 234 | baz| ++--++ 3 rows in set (0.00 sec) Eamon Daly Unless the string starts wit a '0'. - Original Message - From: dixie [EMAIL PROTECTED] To: MySQL mysql@lists.mysql.com Sent: Friday, April 22, 2005 6:18 PM Subject: extract numeric value from a string. Hi at all, I've this necessity. In a table I've a field popolated by a string where the first (not costant lenght) part are number and the second part caracter. I want extract, in other field, the first part and the second in another field. There is a function to obtained it? Tks in advance Paolo -- dixie [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: table handle error
prathima rao wrote: hai, when ever the power goes or the system restarts when im entring data from my vb application to mysql 4 database it gets corrupted and i loose some data at the end please can any one help me in this regards prathima rao Get a backup powersupply with shutdown software. When the power goes out, stop entering data. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql syntax
Rich Brant wrote: I forgot the important part: what I want is to filter on a userID in the person table such as - SELECT u.Username, p.UserID FROM Users u LEFT OUTER JOIN Person p ON u.UserID = p.UserID WHERE (p.UserID = 5) OR (p.UserID IS NULL) THis will return both the matching recs from the user table and NULLs from the person table in sql server, but is what I cant get to work in mysql... WHERE u.UserID = 5 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto-increment by a specific number
StinkyPup wrote: How do I auto-increment by a specific number. For example by 100: You dont. IDData 100 blah blah blah 200 blah blah foo ALTER TABLE PRODUCT AUTO_INCREMENT = 100 doesn't do what I want to do. TIA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: using MyAdmin to build the MySql-Having trouble setting auto-increment for primary key
Dana Terrell wrote: I am new to MySql and I am having troubles getting the primary keys to do what I want. Here is the situation. I am building a database where there are 2 types of users that can access and change information. Because each type of user will be accessing a different part of the database, I wanted to set it where Type 1 users got a primary key UserID that was even, while Type 2 users got a UserID that was odd. Add another column for user type. Encoding special meaning to certain values of an otherwise unrelated column is a bad idea. I have set the auto-increment value in both registration forms to 2 but when I set the default in the type 1's to 2 and the type2's to 1, it does not work and they are both getting the same numbers for their user id. Auto increment fields increment by one, not the default value. Is there something I am missing or is there a different field that I need to set to make this work right or is this something that can't be done in MySql? Any help would be appreciated. P.S. I am more familiar with MSSqlServer than I am with MySql, if that helps. I know how to do this in MSSqlServer but I am lost here. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is there a way to spell check a DB/column?
Ludovic Coumétou wrote: Hello, I have been googling and asking on several forums with no luck, so I'm coming here for the ultimate answer :) eheh Is there a way to spell check a mysql column (or at least table)? Thanks in advance Ludovic Several. 1. Dump - spell check - reload. 2. Write a program to read - spell check - write. 3. Write a UDF that spellchecks a column. The best solution is to make sure your data is correct BEFORE inserting it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Still having problems with MySQL 4.1 on Fedora Core 3
C.F. Scheidecker Antunes wrote: Hello all, I now have disabled my SELinux and so it should work. However it still does not work. I have a bunch of errors now on my .err file that I am copying bellow. I've tried to create the tables again by issuing mysql_install_db and then tried to run the daemon. Is there any ideias on how to fix this? Thanks in advance: errors from .err file: InnoDB: Last MySQL binlog file position 0 79, file name ./presario2700-bin.04 050414 11:23:49 InnoDB: Flushing modified pages from the buffer pool... 050414 11:23:49 InnoDB: Started; log sequence number 0 43724 050414 11:23:49 [ERROR] /usr/sbin/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 050414 11:23:49 [ERROR] Fatal error: Can't open privilege tables: Can't find file: './mysql/host.frm' (errno: 13) 050414 11:23:49 mysqld ended Error 13 is a file privileges problem. Mysql probably does not own the files. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ROW_SIZE or something alike
Marco Neves wrote: oix ppl, Nobody have any sugestion that can me help with this issue? thanks mpneves On Wednesday 16 March 2005 14:09, Marco Neves wrote: Look under storage requirements in the manual. You are MySQL core certified? oix ppl, sorry for the question, but I looked in the Manual online and didn't find nothing that do this that I'm looking for, so I'm hopping someone can help me. I'm looking for some way to know the size each row of a table uses in my database (phisical - real disk space allocated or logical - datasize ignoring compression and any control data, don't mind, anything is better than nothing). The way I was thinking this could be got was with something like: SELECT ROW_SIZE(),* FROM tablename WHERE somefield=somevalue; or SELECT group__id, SUM(ROW_SIZE()) grpsize FROM tablename GROUP BY group__id; This is what I thought would be great to have, but any other way to get a row size would be good enought. This could be both Data and Index size for each row, but data would be good enought. There is anyway to get this information? Thanks, mpneves -- Marco Paulo Neves MySQL Core Certified Linux Certified Professional http://themage.bliker.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: {Spam?} Cannot start replication - can someone help?
Chris Mason wrote: I have two servers, server5.mydomain.com and server8.mydomain.com. I want to replicate one database on server5 to server 8. I did the whole proceedure as recommended in http://dev.mysql.com/doc/mysql/en/replication-howto.html - 6.4. How to Set Up Replication I setup the GRANT statement on server5 for the slave. mysql SHOW GRANTS FOR [EMAIL PROTECTED]; +--- ---+ | Grants for [EMAIL PROTECTED] | +--- ---+ | GRANT RELOAD, SUPER, REPLICATION SLAVE ON *.* TO 'server8'@'MyServer8IP' IDENTIFIED BY PASSWORD 'xxx' | +--- ---+ 1 row in set (0.00 sec) But when I stop and start the slave, I get: 050316 8:56:42 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.04' at position 79, relay log './server8-relay-bin.01' position: 4 050316 8:56:42 [ERROR] Slave I/O thread: error connecting to master '[EMAIL PROTECTED]:3306': Error: 'Unknown MySQL server host 'server5.picado.com' (1)' errno: 2005 retry-time: 60 retries: 86400 When I try from the command line, I get it to work without problem: [EMAIL PROTECTED] mysql]# mysql -h server5.domain.com -u server8 -p Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 30 to server version: 4.1.10-standard-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql You have master set to server5.picado.com, not server5.domain.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select timestamp + 0
Mister Jack wrote: Hi, i'm using the 4.1.10 version of mysql. If I do : select max(timestamp + 0 ) as timestamp from news; ++ | timestamp | ++ | 20050314194920 | ++ so i got the full timestamp(14), but if I do : select max(timestamp) + 0 as timestamp from news; +---+ | timestamp | +---+ | 2005 | +---+ Did i miss something in the documentation ? or is it normal ? thanks What does select max(timestamp) as timestamp from news; return? What would you get if that string was converted to a number? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select timestamp + 0
Mister Jack wrote: It returns : select max(timestamp) as timestamp from news; +-+ | timestamp | +-+ | 2005-03-14 19:49:20 | +-+ The string shown above converted into a number is 2005. ( Unless you think ist should be 1988 ) Add 0, and it is still 2005. and also : select timestamp as timestamp from news limit 1; +-+ | timestamp | +-+ | 2002-03-25 19:45:32 | +-+ so If I do : select timestamp + 0 as timestamp from news limit 1; ++ | timestamp | ++ | 20020325194532 | ++ So i would expect a max(timestamp) + 0 to work the same than without the max. is this a bug ? (the code rely heavily on a result as a timestamp(14), like MMDDHHmmss, so getting this work helps migrating from 4.0 to 4.1) thanks for your help -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: To extend InnoDB table size
Naveen C Joshi wrote: Hi, I have a InnoDB database with MySQL version 4.1.0-alpha installed on my RedHat Linux 9.0. The data files name is like *.frm, *.MYD, *.MYI. These are MyIsam, not InnoDB. Currently the table size is fixed 4GB and I want to extend it 10GB. Could you please give me perfect command to extend my table size. The is my production server so I am restricted to do any exercise. Thanks Naveen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Problem
Xristos Karvouneas wrote: Dear All, I am faced with the following problem: I have got three tables - book,author and authorbook - containing information about books and authors (some books have multiple authors). I want to do a query that would print information like: Title 1 Author 1 Author 2 Title 2Author 3 Author 4 I have written the following: select distinct title, name from authorbook,book,author where authorbook.authorid=author.authorid and book.bookid=authorbook.bookid; You want to add: ORDER BY title,name; You probably are getting them all, but not in the order you expect. hoping that it will do what I want, but I am only getting the first author for each book (probably because of the distinct keyword). Is there any way I can modify the query so that it does what I want it to do? I look forward to hearing from you soon. Thanks in advance. George _ Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems installing MySQL 4.0.24 on a Fedora Core 3 x86 box
C.F. Scheidecker Antunes wrote: Hello Mr Paharenko, I have started it with mysqld_safe and I also tried /etc/rc.d/init.d/mysql start Funny thing is that after install the the mysql and test databases are empty so I've tried mysql_install_db which runs but the databases remains empty as well. It is weird. I've done so many MySQL installations under Linux and they are usually so smoth. Any version I try to install on this machine such as 4.0.24 and 4.0.20 is not successful. So I believe that there must be a missing library on this machine. It is a dual PIII Xenon 500Mhz Compaq Server with 2GB of RAM and a Raid system. It is running a smp kernel 2.6.10-1.770_FC3smp Fedora supplied RPMS initialize the database when you do a 'service mysqld start' Have you done this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: EXPLAIN does not explain the WHERE clause
Christopher Malton wrote: When I use the statement: EXPLAIN SELECT * FROM workunits WHERE Sent0 It returns +---+--+---++-++-++ | table | type | possible_keys | key| key_len | ref| rows| Extra | +---+--+---++-++-++ | workunits | ALL | [NULL]| [NULL] | [NULL] | [NULL] | 2297055 | where used | +---+--+---++-++-++ Which is the same as performing: EXPLAIN SELECT * FROM workunits You have no keys, so all 2297055 rows must be read in either query. If you have only a few possible values for sent, a key may not help. But If I SELECT * FROM `workunits` WHERE Sent0 I Get: ++---++---++---++--++++ | wuid | ETilt | ETilt2 | MTilt | MTilt2 | STilt | STilt2 | Sent | ProcessByHost1 | ProcessByHost2 | ProcessByHost3 | ++---++---++---++--++++ | .11.6267.1 |22 | 22.1 | 1.6 | 2 | 6 |7.1 |2 | 2 | 1 | 0 | | .11.6267 |22 | 22.1 | 1.6 | 2 | 6 | 7 |2 | 2 | 1 | 0 | | .11.6266.9 |22 | 22.1 | 1.6 | 2 | 6 |6.9 |2 | 2 | 1 | 0 | | .11.6266.8 |22 | 22.1 | 1.6 | 2 | 6 |6.8 |2 | 2 | 1 | 0 | | .11.6266.7 |22 | 22.1 | 1.6 | 2 | 6 |6.7 |1 | 2 | 0 | 0 | | .11.6266.6 |22 | 22.1 | 1.6 | 2 | 6 |6.6 |1 | 2 | 0 | 0 | ++---++---++---++--++++ Only 6 rows in set I am using MySQLd 3.23.44 If I should upgrade which is probably a good idea, what version should I get? Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Losing database information
[EMAIL PROTECTED] wrote: ALL, I have an issue where MySQL has lost all of the table information for an existing database, I do not know of anything that has changed and there was no maintenance being done in MySQL. Below you will find the error message as I receive it from MySQL. ANY ideas or suggestions on how to recover this database intact will be GREATLY appreciated. The .FRM files are intact and all permissions have been checked against a database that I CAN still access, which tells me that the problem is the database somehow and not MySQL as a whole. Try check table and repair table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ask for input during SQL script execution
[EMAIL PROTECTED] wrote: All, Can someone point me in the right direction ? I am trying to right sql scripts for queries that will prompt for information. Here is the gist of it, I have a sql script that will query a database however I need the script to ask the user for IP address, and other information that will be different each time this script is run, is there a way to have the script take input from the user? Chris Hood If you are writing scripts, you are writing them in some scripting language, and you will have to use the input facilities of that language. Perl, PHP, sh etc all have the facilities you need. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DELETE FROM statement seems not to use my index...
Ruben Edna wrote: Here is create table def: CREATE TABLE `tblmainupdateinfo` ( `MainID` int(11) unsigned NOT NULL default '0', `ClientID` int(11) unsigned NOT NULL default '0', `UpdateInfo` tinyint(4) unsigned NOT NULL default '0', PRIMARY KEY (`MainID`,`ClientID`), KEY `ClientID` (`ClientID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 The tables holds about about 7 million records. Different MainID's are at about 40.000 and different ClientID's at about 600 (table will max out at about 24 million records). On this table I often do the following: DELETE FROM tblmainupdateinfo WHERE ClientID=XXX; I would expect MySQL to then use the ClientID Index. However I don't think it does... because on my laptop it takes all between 10-60sec to delete (depends on how many WHERE clause matches). When I remove the PRIMARY index the delete takes about only 0.2-2.0sec. I assume it then acutally uses the ClientID index. Why does it not when the PRIMARY index exists? I have also tried to set the PRIMARY KEY in reverse ordrer (`ClientID`,`MainID`), still having the ClientID index and then it also takes only about 0.2-2.0sec. It seems to me that the DELETE statement only tries to use the first index available whatever else index that exists... Might this be a bug...? Yes, I have tries to use EXPLAIN SELECT... and then it does it correct like this: In addition to deleting the record, both indicies must be updated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem in LOAD DATA INFILE
Alpesh Kothari wrote: Hi, I am facing problem while inserting more than 20,000 records using LOAD DATA INFILE statement through comma seperated file. Here is my statement: LOAD DATA LOCAL INFILE '/home/mysql/isa_logs/a.txt' INTO TABLE FirewallLog_1 FIELDS TERMINATED BY ','; I have persistantly observed that when in try to run this statement with a file having 20,000 records. The problems starts when records in the files are more then 20,262 ERROR 1030: Got error 28 from table handler This is the error I get. MySQL Version: 4.0.13 Host: Linux - Red Hat 7,0 Is this related to my sql global variables or hardware resources ? Thanks in advance. Regards, Alpesh Kothari Oracle DBA Net4Nuts Limited [EMAIL PROTECTED] bash$ perror 28 Error code 28: No space left on device Free up some disk space, get a bigger disk, or move your data or temp directory. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Comparison SQL always return false
sam wun wrote:
Hi,
I use MySQL 5.0 with perl (the latest version) in FreeBSD 5.3.
I found that if I assign a hard coded string (with double quote) to
the string variable which in turns become part of the SQL statement,
the SQL does return result.
If I assign a value which is return from an array in perl (eg.
@array[1]) and use this variable in the SQL statement, the SQL does
not return any result.
$array[1] not @array[1].
For example:
The following code does not return any result
my $mycustcode = @outlets[1]; # this array contains string 07-2-0057
However, if I use the following hard coded assignment, the SQL
statement returns result:
# my $mycustcode = 07-2-0057;
$create_view_sql = qq {create view $viewtab as
select c.custcode, c.custname, c.type,
sum(t.netsales) as sales
from customer c, transaction t
where c.custcode = t.custcode
and date(t.date) = $start_date
and date(t.date) = $end_date
and (c.type = EXPORT or c.type = LOCAL)
and (c.custcode = $mycustcode)
group by c.custcode;};
I may be using incorrect way to split the tokens, here is the function
to extract a list of the token:
$outlet_str = $in{'outlets'}
Thanks
Sam
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql_install_db problem!!
manasvini nandakumar wrote: Hi all, I have cross compiled mysql-4.1.7 (from the source distribution)for an IXP425 based board with Montavista Linux-3.0.When I run mysql_install_db --user=mysql ...It runs into several errors with the following log [EMAIL PROTECTED]: /usr/local# ./bin/mysql_install_db --user=mysql WARNING: The host '192.168.10.231' could not be looked up with resolveip. This probably means that your libc libraries are not 100 % compatible with this binary MySQL version. The MySQL daemon, mysqld, should work normally with the exception that host name resolving will not work. This means that you should use IP addresses instead of hostnames when specifying MySQL privileges ! ERROR: 3 Error writing file './mysql/db.frm' (Errcode: 2) ERROR: 1146 Table 'mysql.db' doesn't exist ERROR: 1146 Table 'mysql.db' doesn't exist ERROR: 3 Error writing file './mysql/host.frm' (Errcode: 2) ERROR: 3 Error writing file './mysql/user.frm' (Errcode: 2) -- The filesystem is NFS mounted.I am hoping someone can tell me why this is happenning and If I have missed out something. The mysql user cannot write to the database directory. It could be an NFS issue. Thanx in advance, Manasvini - Do you Yahoo!? Yahoo! Mail - You care about security. So do we. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Relative efficiency (in terms of disk io) between REPLACE and UPDATE w/InnoDB
John McCaskey wrote: I'm currently doing a large number of REPLACE queries, I know that these evaluate as if doing a DELETE/INSERT pair, and I'm wondering if this is true on a disk io level as well with extra io occuring for the delete, and then re-insertion, vs what would occur with an UPDATE. The way it works roughly each row gets updated around 12-24 times, the updated do not affect the primary key, or any of the other keys for that matter. The table is INNODB. So I'm thinking if the row is deleted then re-inserted, there is the overhead of one finding the old row and marking it deleted, two searching for the correct pos for the row in the table and in the key structures, and three writing the row to disk. Vs, and UPDATE would have the overhead of one finding the old row, and two writing the updates to disk. As such it seems like it actually be faster for me to attempt an UPDATE, and if it fails, then do an INSERT, rather than using REPLACE? This provides a window for errors. You can attempt the update, and before the insert, another process could insert the record. And insert followed by an update on failure closes this window. Is this correct? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQL is getting slow
Patrick Marquetecken wrote: Hi, I have 3 snort sensors logging to a central mySQL database after two weeks the size of the database is about 3.3GB and the machine is getting slow, as i'm not used to be working with mySQL is my question how far can i go before the machine stop responding ? HP Pentium4 - 2.2mhz - 512 mb, 99% used and no swap, HD of 40GB. I f that is 99% disk used, you are already dead. That is why it is slow. You have no room to do anything. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: spaces in table/column name
sharif islam wrote: How mysql deals with spaces in table / column name? I am also using mysqlcc. If I try the following in the doesn't work. Creating table name with spaces from mysqlcc didn't give any error. But the following does: INSERT INTO 'tbl name with spaces' (col1, 'col name with spaces') VALUES(15,16); This is really bad form and should be discouraged. If you absolutely must do this, use back-ticks, not quotes, and be prepared for a never ending series of headaches later. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: update and concat
Thomas McDonough wrote:
I'm trying to set all the values of column 'map' to the value of
column ML and '.png'. My intuition and an extensive reading of the
manual and mail archives tell me to do it like this:
mysql update listings set map= concat (ML, '.png') where ML'';
mysql update listings set map= concat(ML, '.png') where ML'';
Lose the space between 'concat' and '('
but all I get is this:
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 '(ML, '.png') where ML''' at line 1
What am I doing wrong?
Tom
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySql Hangs
Ajay Kalambur wrote: Hi We have been having problems with a MySql database that runs on Linux.It just occurred suddenly and was working fine before Details: MySql Version:# mysql Ver 14.7 Distrib 4.1.6-gamma The filesystems which are accessed by MySql just hang.We cannot access any of the tables.All clients just hang.When we Shutdown the server, it hangs again.The Mysql deamons wont get killed even if I use the kill -9 command.After a reboot Everything works fine for sometime and then goes bad again.The problem seems to be that mysql seems to have a lock on all the tables and hence the use database,select,update hangs.All of the Mysql configuration is with default options. Has anyone encountered a similar problem with the 4.1.6-gamma version?? Thanks Ajay Next time it hangs, check your disk space, especially your tmp directory. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Headers for queries with empty return sets
Eric Yeh wrote: Hello, I am a new user of MySQL and have a simple question I was hoping the list could help me with. I have been a long time Sybase user and am now looking at converting some of my scripts to MySQL. I ordinarily run scripts as batch from the command line, and parse the results in a proprietary language. My parsers recognize csv-style files (first row as a header, everything delimited by some character), which generally works well with the MySQL batch output. However, I have observed the exceedingly strange functionality that when the result of a query is empty, MySQL returns nothing, instead of just the header with no data. This is mathematicaly inconsistent behavior, and of course breaks my scripts. Since it is unexpected behavior, though, I expect MySQL should have an easy option or some such to get around this. I have tried --column-names, --skip-column-names or -N -t, -vvv, etc, and nothing seems to work. Any suggestions, short of parsing my query itself for the headers? Thanks, Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Checking the status of a field, and ensuring it is not updated before I change it
Joshua Beall wrote: Hi All, I have the following situation: I want to check a row in a database (list of jobs that need to be run). So, my script checks the status field, and if it is not started, then it marks it as locked and running, and when it finishes, it marks it as completed. Now, here is what I am concerned about - because there may be many processes running at any one time, it seems feasible to me that two instance of my script might simultaneously issue the SELECT statement, and seeing that the job is not started, then subsequently issue UPDATE statements. Both processes think they are the only ones running. How should I deal with this? It seems to me that I need some sort of query that, all in one transaction, first locks the row so that no other processes can access it, then checks to see if it is running/completed or not. If it is not yet running/completed, it starts the job, updates the database row to mark the job as running, and then unlocks the row. Is this what I should do? I'm not sure how to do this. Any thoughts? Any help would be greatly appreciated! Sincerely, -Josh UPDATE proctable SET status='running' WHERE procid=23 AND status='stopped'; If this fails someone else already started it. OR UPDATE proctable SET status='running', starter='myid' WHERE procid=23 AND status='stopped'; SELECT status,starter FROM proctable WHERE procid=23; Is it running, and do you own it? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: data deleted in MySQL but table file (.MYD) remains same size
Craig Cummings wrote: Hi there, I have a table with three columns, two varchar(12) identifiers and a longtext column with very long (some 50 MB) strings. The size of the chromosomes.MYD table was about 2.8 GB. The table was used transiently and I no longer needed to store the strings, so in the interest of freeing up space I did the following: mysql UPDATE chromosomes SET sequence = ; When I query the database I can see that the sequence field is a null string for each record. However, the size of the chromosomes.MYD file in the data directory has not changed (i.e. it is still about 2.8 GB). SHOW TABLE STATUS (in mysql) and df (at the shell prompt) both report the same value. How can I get the table size to correspond to the small amount of data that actually remains in the table? Thanks for your assistance. Dump it, delete it, and reload it. Files never get smaller, only bigger. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
