Updating mysql database only with delta data

hi I have a huge mysql database on the live server. Every 15 days few 1000 records change in that database [additions/updations/deletions] I don't want to upload the entire mysql database every 15 days as the file size is huge. Is there anyway for me to just upload the delta data and use some

Re: Grants not entirely propagated to slaves?

Atle Veka wrote: What version of MySQL are you using? Also, are you issuing only GRANT .. statements or modifying the privilege tables manually as well? Search for 'GRANT': http://dev.mysql.com/doc/mysql/en/replication-features.html Atle - Flying Crocodile Inc, Unix Systems Administrator On Fri, 1

Re: where is my data?

Hi, check the script /etc/rc.d/mysql which is the SuSE equivalent to mysqld_safe. That's where they set datadir= and you have to change this to reflect your new path. Change the paths to the socket and pid file as well. There should be no problem to move all data to a new place and start the

innodb - in usage

Hi, I had got webserver with mysql 4.0.20 (if I remember well) compiled from sources on slackware 9. Now, I've bought new machine, and I've installed gentoo with mysql 4.0.22. I've copied (in shell) datadir to new machine, preserving attributes. Now, every of my innodb table in phpmyadmin is

Innodb: Alter table progress

Hi, Is there any way of checking the progress of an ALTER TABLE query on an InnoDB table? show innodb status isn't clear. Thanks, -- James Green Systems Administrator, StealthNET Ltd, www.stealthnet.co.uk Tel: 0870 800 1777 Intl: +44 1493 660066 Fax: 0870 135 1069 -- MySQL General Mailing List

how to run a file in MySQL

Hello all, This is probably really basic for all of you but I have been trying to find it in the manual without success... My question is if it is possible when you are logged in to MySQL to run a file with sql-statements in, instead of sit and execute each statement seperatly. The file I

Re: how to run a file in MySQL

The command you need is source filename ; Alternatively, if you are outside the mysql clined mysql filename Alec Joppe A [EMAIL PROTECTED] 04/04/2005 09:59 To mysql@lists.mysql.com cc Subject how to run a file in MySQL Hello all, This is probably really basic for all of

RE: how to run a file in MySQL

Hi Joppe, Use SOURCE or \. to execute the filename: mysql SOURCE E:\SQL\MySQL\test_tables.sql Database changed ++ | Tables_in_test | ++ | a | | academies | . HTH Mark Mark Leith Cool-Tools UK Limited http://www.cool-tools.co.uk

Re: Load data infile and text fields

Hello. Do you use a VARCHAR type for that column? It's maximum length is limited to 255 characters. I think, switching to TEXT type could solve the problem. [EMAIL PROTECTED] wrote: First of all I hope you can be patient for my english I'm working with data import into mysql

Re: Updating mysql database only with delta data

Hello. MySQL supports incremental backups. See: http://dev.mysql.com/doc/mysql/en/backup.html BG Mahesh [EMAIL PROTECTED] wrote: hi I have a huge mysql database on the live server. Every 15 days few 1000 rec= ords change in that database

Re: deadlock with innodb

Hello. What transaction isolation level do you use? By the way - there's a fresh bug related to SELECT ... FOR UPDATE: http://bugs.mysql.com/bug.php?id=9512 Philippe Poelvoorde [EMAIL PROTECTED] wrote: Hello, here is a snippet of my code : BEGIN SELECT ... FROM

Re: MySQL Replication

Hello. Not enough information to make a conclusion. Use SHOW SLAVE STATUS and information from the binary logs to determine the problem. See: http://dev.mysql.com/doc/mysql/en/replication-problems.html David Lloyd [EMAIL PROTECTED] wrote: Hi There, I have a

Re: where is my data?

Hello. You may specify the configuration file to mysqld_safe with --defaults-file command line option. `basedir` variable points to location of MySQL installation. Have you installed MySQL to /var/lib/mysql? When you'll be able to login to the server, what does the following statement

Re: Strange behavior

Hello. I don't have any ideas at least now. But additional information could be helpful. Do you connect from JBoss to the slave or master server? Please use SHOW PROCESSLIST to find in what state the server threads waste their time. If you find something interesting send it. Include also

Re: Newbie :create table multi, index

Hello. Use something like: create table user( UserID int primary key, Password varchar (20), User_stats int); See: http://dev.mysql.com/doc/mysql/en/create-table.html Aji Andri [EMAIL PROTECTED] wrote: hi seniors, I'm trying to create a table, here my table

Re: Grants not entirely propagated to slaves?

Hello. If you replicate the privilege tables in the mysql database and update those tables directly without using the GRANT statement, you must issue a FLUSH PRIVILEGES statement on your slaves to put the new privileges into effect. Nico Sabbi [EMAIL PROTECTED] wrote: Hi, it

Re: Can't create a new thread (errno 11). If you are not out of available memory, you can consult the manual for a possible OS-dependent bug'

Hello. I don't know. With additional information we could make more exact conclusions. Jocelyn Fournier [EMAIL PROTECTED] wrote: Hi, For me it sounds like a glibc issue. BTW, currently the 4.1.10a build is compiled against glibc-2.2, does MySQL plan to build next releases

MySQL to MSSQL

I posted a while back for a solution to make a MySQL DB into a MSQL DB can anyone help please? Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: How to find missing record?

Jeremy Cole wrote: Hi, Both tables should hold the same number of records. However, I've discovered that 'Close' is one less than 'Open' (1693 vs 1694). How can I find out which record is missing from 'Close'? I know it's not the case of an extra entry in 'Open' because 1694 divides evenly by

resolving ambiguous column name in subquery

Hi, the following seems wrong to me. Not sure whether this is a bug. In short: a column-name in a subquery can refer to a table *outside* of the subquery (fair enough), but if the column-name is ambiguous between *inside* and *outside*, the parser assumes that it refers to the *inside* context.

float type / concat

Hi list, I'm using php/mysql, I was updating a table through phpmyadmin then I saw that to update all the columns which type is declared to float the developper of phpmyadmin have added a concat. Something like: UPDATE `tableInduction` SET `inductionType` = 'screening' AND CONCAT( `volume` ) =

Line Breaks Problem

Hello, I would like to describe the following problem and get an opinien from list members. My database values contains line breaks ('\r\n'). For example I have 1 row with 'value\r\n' in 'column_name'. SELECT HEX(column_name)FROM table_name; will return 76616C75650D0A I'm

Re: resolving ambiguous column name in subquery

I take it all back. I see now why this behaviour is desirable, or at least standard. E.g., see: https://aurora.vcu.edu/db2help/db2s0/c2corr.htm On Apr 4, 2005 2:40 PM, Tom Cunningham [EMAIL PROTECTED] wrote: Hi, the following seems wrong to me. Not sure whether this is a bug. In short: a

OS X and MySQL table corruption...

I have been running into issues with MySQL table corruption issues on a couple of OS X systems. I end up with tables that need repair every day and some times multiples times per day. It's so bad now that I have a script that runs the mysql 'REPAIR TABLE...' command and then the myisamck

subquery substitute in 4.0?

How can i do the following with 4.0? delete fom t1 where id in (select id from t1 where usr_id = 10 order by date_inserted limit 4,999) The ideia is to limit to 4 rows with the same usr_id value before i do an new insert with that user_id. Ending up with only 5 rows. I'm trying to not do a

Re: deadlock with innodb

Gleb Paharenko wrote: Hello. What transaction isolation level do you use? By the way - there's a fresh bug related to SELECT ... FOR UPDATE: http://bugs.mysql.com/bug.php?id=9512 tx_isolation is set to : REPEATABLE-READ (which is the default) I've stripped everything uneeded from my code, and

Sparc vs. x86 Solaris MySQL compatibility

Hi, We're running MySQL on Sparc Solaris now, but are considering moving to an Opteron Solaris box for price/performance reasons. Does anyone have any comments about MySQL's relative stability or performance on the two platforms? Also, are MySQL databases binary-compatible on the two

Re: subquery substitute in 4.0?

In article [EMAIL PROTECTED], Gabriel B. [EMAIL PROTECTED] writes: How can i do the following with 4.0? delete fom t1 where id in (select id from t1 where usr_id = 10 order by date_inserted limit 4,999) Put the result of the inner SELECT into a temporary table and then use the multi-table

Re: Load data infile and text fields

Michael, my problem is that I need a filed with precision for a field of exactly 595 characters! Only text field type with precision is the char type but its limit is 256 char. I've tried with text type, but precision were been ignored and my sql silently truncate it at 256 value. I solved my

Problem doing insert on a datetime field

I am having a problem doing an insert on a datetime field. Is there something I have to use to convert it to that form? I have it set up in a string as '2000/09/17 00:00:00' but that does not seem to work. Thanks for any help you can offer. Bob Rawlinson -- MySQL General Mailing List For list

Re: where is my data?

Thank you very much. that was a real life saver. u rock :) Regards Kaustubh Original Message Follows From: Thomas Spahni lt;[EMAIL PROTECTED]gt; To: kaustubh shinde lt;[EMAIL PROTECTED]gt; CC: mysql@lists.mysql.com Subject: Re: where is my data? Date: Mon, 4 Apr 2005 12:03:05 +0200 (CEST)

Writing a query to load all files from a local directory into a table as blob

If my poor english assists me, I'd like to ask you about how can I import a lot of small msword files into a mysql table where I've created a column definied as longblob. I'm using mysql 4.1 and I would avoid to start studing php or other language at this moment. In other word, my situation is: 1)

Re: where is my data?

On Apr 2, 2005, at 7:58 AM, kaustubh shinde wrote: Hi, I have suse 9.2 and MySQL 4.21 My basedir is /var/lib/mysql datadir /data/mysql/mysqldata As it stands now, mysqld_safe is broken when you move your data directory, so this may cause problems. (See bug 7249

Can a Function return multiple values?

I am trying to return multiple values from a Function, not sure if it is allowed: delimiter | create function cf_test() returns integer, varchar(255)/* tried with semicolon after varchar(255) */ begin declare p_col2 smallint; declare p_col3 varchar(255); select col2, col3

ERROR 1044: Access denied for user: '@localhost' to database 'mysql'

Howdy all, I keep getting this error when I try to issue: mysql mysql -u root -p the same thing happens when I substitute any other database name, not just mysql. I have another,nearly identical machine from which I copied the contents of /var/lib/mysql/mysql/ . The permissions all appear to

Re: OS X and MySQL table corruption...

I've been running on OS X for a while, although I haven't had any really heavy usage sites. I also haven't had a single corruption problem on a live database in the 2+ years I've been running MySQL. Now, I say on a live database. For the first time I tried upgrading the MySQL that comes with

never mind.. works now.. 'localhost' not matched by '%' for some reason.

-Original Message- From: Samuel Flores [mailto:[EMAIL PROTECTED] Sent: Monday, April 04, 2005 12:52 PM To: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: ERROR 1044: Access denied for user: '@localhost' to database 'mysql' Howdy all, I keep getting this error when I try to issue:

Temporal databases MySQL

Greetings everyone, I haven't been able to find any piece of information on temporal databases and MySQL except the code from the TAU Project which seems to be experimental at best. Not even discussions on the mailing lists or on the forums but while I was looking for temporal, people may have

FULL OUTER JOIN

Hello all, mysql 4.0.20 I'd like to know how one can do a full outer join. I've read some workaround with a UNION, but i need the join only on a few columns, while UNION will make double tuple if one column is not the same. I also would like to avoid temporary table if possible, since the

Performance Tuning - Table Joins

I have been struggling to maintain decent performance on a web/database server for a good 6 months now due to MySQL performance issues. I have decided that my best option at this point is to take it to the list, so in advance, I thank you all for taking a look. There is no error messages that

Re: OS X and MySQL table corruption...

Ave, I run MySQL 4.x on my Power Mac G5 with Mac OS X 10.3.8 with PHP5. I have been running Apache Web Server and my websites on this machine for almost 6 months now. And twice I have faced table corruption which I had to fix using REPAIR TABLE. Twice in 6 months isn't bad at all, yet, I wonder

Re: Performance Tuning - Table Joins

On Apr 4, 2005, at 1:22 PM, Jason Johnson wrote: I have been struggling to maintain decent performance on a web/database server for a good 6 months now due to MySQL performance issues. I have decided that my best option at this point is to take it to the list, so in advance, I thank you all for

Re: OS X and MySQL table corruption...

Hello, We have been running MySQL (about 50 databases, some medium-sized, some small, mix of MyISAM and InnoDB) on Mac OS X Client for more than a year (currently 10.3.8 with MySQL 4.1.10) and I have never seen any corrupt tables. We use only the MySQL-provided packages for our binaries. Maybe

Re: Performance Tuning - Table Joins

The premise of the query is to return required continuing education hours for the entire membership of the organization. Limited to one member when providing a membership ID. The query is a little bulky, and fortunately I cannot take credit for its design, but here goes (keep in mind that

Re: where is my data?

Hi Michael, Thanks a lot for a very informative and educational reply. The problem was that my base directory is /usr i.e. i have /usr/share/mysql/english.. and I was assuming it was /data/mysql or wherever i chose to put my datadir. I was clearly confused abt the concept of basedir. So , now

Re: Problem doing insert on a datetime field

Robert A. Rawlinson wrote: I am having a problem doing an insert on a datetime field. Is there something I have to use to convert it to that form? I have it set up in a string as '2000/09/17 00:00:00' but that does not seem to work. Thanks for any help you can offer. Bob Rawlinson Sorry! I

mysqlbinlog troubles.

hello, I'm trying to restore a table from a full back up and then a binlog. this is a test table setup specifically for this. I have 34 rows in the full backup, another 5 in the binlog. I find the date of the last insert and use this as the --start-datetime for mysqlbinlog. The problem is that

Re: Performance Tuning - Table Joins

At 12:22 PM 4/4/2005, you wrote: I have been struggling to maintain decent performance on a web/database server for a good 6 months now due to MySQL performance issues. I have decided that my best option at this point is to take it to the list, so in advance, I thank you all for taking a look.

Re: Load data infile and text fields

Stefano, I'm copying this to the mailing list. I think it is a lot better if we have discussions of this kind on the mailing list so that others can also learn from them, either now or in the future via the mailing list archive. I'm glad to hear that you solved your data loading problem. You've

Cannot execute query - Can't find file: (error: 9)

Cannot execute query. snip my SQL statement Can't find file: './donor/list_lst.frm' (errno: 9) - - I got the same error last week on a different table. Today I notice that there is a table in another database on same system producing the same error. I attempted to access mysql cli,

RE: Performance Tuning - Table Joins

Your not indexing properly this should be a blink of a search. Or your looping your loops when you search. Thanks Donny Lairson President 29 GunMuse Lane P.O. box 166 Lakewood NM 88254 http://www.gunmuse.com 469 228 2183 -Original Message- From: mos [mailto:[EMAIL PROTECTED] Sent:

subqueries *not* using indexes for IN clause

http://www.peerfear.org/rss/permalink/2005/04/02/BrokenMySQLSubqueries Whats up with this? As far as I can tell MySQL subqueries in 4.1.x releases are totally broken with IN clauses The major reason is that they don't use *ANY* indexes and resort to full table scans. Lets take two queries:

Re: subqueries *not* using indexes for IN clause

We have noticed this as well and it is really pretty shoddy. It seems that when using IN( SELECT ), they treat it as ANY() which does a full table scan. Only way we have found to get fast performance out of subqueries is to use the derived table format and join with the derived table.

Setting up a Chemical database

I have a customer who has sent my close to 300 chemical products in word format? How do I translate this into mysql tables? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Setting up a Chemical database

One table should be sufficient to handle this with one record for each product. - Asad On Mon, 4 Apr 2005, Dave Shariff Yadallee - System Administrator a.k.a. The Root of the Problem wrote: I have a customer who has sent my close to 300 chemical products in word format? How do I translate

Re: Setting up a Chemical database

I'm afraid we can't tell you anything with virtually no information given by you. What is a product? Which are the informations you want to hold about a product? How do they relate to each other? What is the purpose of the database? Which sort of informations are people supposed to get out of

Re: Setting up a Chemical database

Dave, I have a customer who has sent my close to 300 chemical products in word format? How do I translate this into mysql tables? Save it from Word as comma- or tab-delimited, create a MySQL database table, use ODBC Admin to create a DSN for that database, open the MySQL database in Access,

Is this wise use of auto_increment?

Hello, I'm using MyISAM tables in mysql and in order to make a certain operation appear atomic, I need to insert records into 2 tables in a certain order. In particular, in one of the tables is a key that maps to multiple rows in the other table. This is the sane thing to do if there were'nt any

Users and Max_questions

Is there a way to determine the question count of an individual user?I use the max_questions grant extensively in order to manage server resources and this would help me out a lot. Regards, Gary Huntress -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To

Re: Hide password when running mysqldump from a batch

This should work for mysqldump just as it does for mysql. What, precisely, do you mean by does not appear to work? Describe what happens. Do you get an error, or unexpected results? If an error, what's the error message? If unexpected results, what do you expect, and what do you get? Are

Re: Load data infile and text fields

On Apr 4, 2005, at 3:52 PM, Rhino wrote: Stefano, I'm copying this to the mailing list. I think it is a lot better if we have discussions of this kind on the mailing list so that others can also learn from them, either now or in the future via the mailing list archive. I'm glad to hear that you

Re: subqueries *not* using indexes for IN clause

Greg Whalin wrote: We have noticed this as well and it is really pretty shoddy. It seems that when using IN( SELECT ), they treat it as ANY() which does a full table scan. Only way we have found to get fast performance out of subqueries is to use the derived table format and join with the

Is it any faster to use IGNORE with Load Data Infile?

I'm loading 100 million rows into a MyISAM table and I'm wondering what overhead is there when using the Load Data Infile REPLACE over Load Data Infile Ignore syntax. For example, does the REPLACE do a lookup prior to inserting the row? Would it be faster to use Ignore? There is no

Re: Innodb: Alter table progress

James, - Original Message - From: James Green [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, April 04, 2005 1:28 PM Subject: Innodb: Alter table progress Hi, Is there any way of checking the progress of an ALTER TABLE query on an InnoDB table? show innodb status

cannot connect to the mysql server.thank you.

dear sir, nice to meet you. now i have installed the MySQL-ServerClient(4[1].0.15).but i cannot connect mysql server when i make a change. what is the wrong with what i do?l can i get your help? thank you very much. -- MySQL General Mailing List For list archives: