table status: Create_time
Hello all, Is there a way to change a table's Create_time that is displayed when doing a `SHOW TABLE STATUS FROM mydb LIKE 'foo'\G`? I've tried using touch to modify the file dates directly and searched the online docs with no luck. The reason I want to do this is because we had some tables that were dangerously close to running out of room. The tables are in excess of 5-8 million rows and it is not possible to lock them for the time it would take to alter the MAX_ROWS and AVG_ROW_LENGTH so I renamed them to a temporary name and created a new table to be used for the duration of the alter. Once the alter statement was complete I switched the names and INSERT...SELECT from the temporary table into the newly altered table. This changed the table's Create_time which breaks a program we have that estimates how many days, months, or years we have before a table is full based on the number of rows and the Create_time. We have far too many tables to just alter them all in one sitting so we do them as needed. Thanks. -- Jim Grill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication, table db already exists
[EMAIL PROTECTED] wrote:
Hello All,
Here is a problem I have been trying to debug for a few days. I had a mysql
server running for about a month. I did a mysql dump of all the tables on the
master and setup a second machine to be the slave, loading all the tables. I
set the replication stuff up in the slave my.cnf. Now as soon as I start the
slave I get good data flow between the two machines but the slave does not
update with the error below. Is this because I did not do a binary snapshot of
the master machine to setup the slave? I originally was not going to setup
replication, just going to do dumps of tables, but then changed directions.
Thanks! If you need more info, please ask.
--ja
mysql> show slave status \G;
*** 1. row ***
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.100
Master_User: repster
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: rhea-bin.17
Read_Master_Log_Pos: 405948310
Relay_Log_File: dione-relay-bin.02
Relay_Log_Pos: 121
Relay_Master_Log_File: rhea-bin.01
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1050
Last_Error: Error 'Table 'db' already exists' on query.
Default database: 'mysql'. Query: ' CREATE TABLE db ( Host char(60) binary
DEFAULT '' NOT NULL, Db char(64) binary DEFAULT '' NOT NULL, User
char(16) binary DEFAULT '' NOT NULL, Select_priv enum('N','Y') DEFAULT 'N'
NOT NULL, Insert_priv enum('N','Y') DEFAULT 'N' NOT NULL, Update_priv
enum('N','Y') DEFAULT 'N' NOT NULL, Delete_priv enum('N','Y') DEFAULT 'N'
NOT NULL, Create_priv enum('N','Y') DEFAULT 'N' NOT NULL, Drop_priv
enum('N','Y') DEFAULT 'N' NOT NULL, Grant_priv enum('N','Y') DEFAULT 'N'
NOT NULL, References_priv enum('N','Y') DEFAULT 'N' NOT NULL, Index_priv
enum('N','Y') DEFAULT 'N' NOT NULL, Alter_priv enum('N','Y') DEFAULT 'N'
NOT NULL, Create_tmp_table_priv enum('N','Y') DEFAULT 'N' NOT NULL,
Lock_tables_priv enum('N','Y') DEFAULT 'N' NOT NULL, PRIMARY KEY Host
(Host,Db,User), KEY User (User) ) comment='Database privileges''
Skip_Counter: 0
Exec_Master_Log_Pos: 79
Relay_Log_Space: 11454541057
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 12172794
1 row in set (0.00 sec)
I'm guessing but... It looks like you did not set the master log
position properly. Your slave is at position 79 in the master log. You
can tell by looking at Relay_Log_Space and Seconds_Behind_Master (140
days) that is it far behind the master. That would account for the
couple of months that the server was up before you decided to replicate it.
Did you get the log position of your snap shot and use `change master
to` to sync the slave to the correct position? You can either reset the
master logs using RESET MASTER or you need to do a SHOW MASTER STATUS to
get the log file position at the time of your snap shot. When you set
up the slave you can provide the log file position or, if you RESET
MASTER, you can set MASTER_LOG_FILE='the_name_the_log_file' and
MASTER_LOG_POS=4, which is the default start position for a fresh binary
log.
Here is some good reading:
http://dev.mysql.com/doc/mysql/en/replication.html
http://dev.mysql.com/books/hpmysql-excerpts/ch07.html
Best of luck to you.
Regards,
Jim Grill
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: aggregate count and group by
Jim Grill <[EMAIL PROTECTED]> wrote on 02/18/2005 03:17:39 PM: Hi, I need some SQL guru help on this one. I'm trying to re factor an existing application where a number of clicks grouped by keyword for two different time periods are needed. For example, a user picks a date range and the application selects a count for how many times a keyword appears in a table for that date range. example: mysql> SELECT keyword, COUNT(id) AS clicks FROM clicksTable WHERE domain = 'mydomain.com' AND click_date BETWEEN '2004-12-01' AND '2004-12-15' GROUP BY keyword HAVING COUNT(id) > 10 ORDER BY keyword; This might return many thousand rows similar to: +---++ | keyword| clicks | +---++ | advertising pay per click | 11 | | advertising statistics| 29 | | keyword management| 13 | | pay per click software| 14 | +---++ The application then loops through the result and issues separate queries for each keyword that are similar to: mysql> SELECT COUNT(id) AS clicks FROM clicksTable WHERE keyword='$keywordFromPreviousQuery' AND domain = 'mydomain.com' AND click_date BETWEEN '2004-12-16' AND '2004-12-31' GROUP BY keyword HAVING COUNT(id) > $countFromPreviosQuery ORDER BY keyword; As you can imagine this makes the app very slow and nearly unusable. Unfortunately, my job is to make it usable again. :-) I'm trying to figure out a way to do this in one query (or at least not have to send tons of queries over the network) but can't figure out how to aggregate the results. I'd like to get a result similar to the following: +---++---+ | keyword| previous clicks | current clicks | +---++---+ | advertising pay per click | 11 | 15 | | advertising statistics| 29 |30 | | keyword management| 13 | 29 | | pay per click software| 14 |19 | +---++-------+ I'm using MySQL 4.1 so perhaps some of the new features might help. ??? Thank you for the help! -- Jim Grill Jim, What you are doing is a form of crosstab or pivot table report based on date ranges. That means that if you want this to be a "dynamic" query you will have to build your statement in your code (it won't be static SQL). But to take your examples and convert them to a single query would look like this: SELECT keyword, , SUM(if(click_date BETWEEN '2004-12-1' AND '2004-12-15',1,0)) as 'previous clicks' , SUM(if(click_date BETWEEN '2004-12-16' AND '2004-12-31',1,0)) as 'current clicks' FROM clicksTable WHERE click_date BETWEEN '2004-12-1' AND '2004-12-15' OR click_date BETWEEN '2004-12-16' AND '2004-12-31' GROUP BY keyword HAVING `previous clicks` > 10 OR `current clicks` > 10; For different ranges (different sets of weeks) you will have to build your SQL statement to match. I know I could have used one large date span in my WHERE statement but that wouldn't have shown you where your column conditions and your WHERE conditions matched up. Using this pattern you can compare this week to last week, this year to last year, or any other sets of ranges. The HAVING statement filters the results so that you get only those keywords that were clicked on more than 10 times during either range. HTH, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Thank you very much, Shawn! That pattern makes perfect sense to me and helps solve my problem. Thank you for taking the time to show me that. Regards, Jim Grill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
aggregate count and group by
Hi, I need some SQL guru help on this one. I'm trying to re factor an existing application where a number of clicks grouped by keyword for two different time periods are needed. For example, a user picks a date range and the application selects a count for how many times a keyword appears in a table for that date range. example: mysql> SELECT keyword, COUNT(id) AS clicks FROM clicksTable WHERE domain = 'mydomain.com' AND click_date BETWEEN '2004-12-01' AND '2004-12-15' GROUP BY keyword HAVING COUNT(id) > 10 ORDER BY keyword; This might return many thousand rows similar to: +---++ | keyword| clicks | +---++ | advertising pay per click | 11 | | advertising statistics| 29 | | keyword management| 13 | | pay per click software| 14 | +---++ The application then loops through the result and issues separate queries for each keyword that are similar to: mysql> SELECT COUNT(id) AS clicks FROM clicksTable WHERE keyword='$keywordFromPreviousQuery' AND domain = 'mydomain.com' AND click_date BETWEEN '2004-12-16' AND '2004-12-31' GROUP BY keyword HAVING COUNT(id) > $countFromPreviosQuery ORDER BY keyword; As you can imagine this makes the app very slow and nearly unusable. Unfortunately, my job is to make it usable again. :-) I'm trying to figure out a way to do this in one query (or at least not have to send tons of queries over the network) but can't figure out how to aggregate the results. I'd like to get a result similar to the following: +---++---+ | keyword| previous clicks | current clicks | +---++---+ | advertising pay per click | 11 | 15 | | advertising statistics| 29 |30 | | keyword management| 13 | 29 | | pay per click software| 14 |19 | +---++---+ I'm using MySQL 4.1 so perhaps some of the new features might help. ??? Thank you for the help! -- Jim Grill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Temporal values VS. int values
Looking for some informed opinions... Which is faster for searching by date ranges? ...Times stored in date() columns - temporal values. ...Or dates stored as unix timestamps in an int(10) unsigned column - int values. Most of the queries will be searching for rows between certain data ranges consisting of up to a few months worth of data at a time. Thanks, Jim Grill
Re: Sync db
Two way replication is possible. However, it does not work like you think it would. It would not be safe to modify data on both databases at the same time. See the FAQ on replication: http://dev.mysql.com/doc/mysql/en/Replication_FAQ.html There is some information regarding two-way replication that will shed some light on the subject. Jim Grill > yeah, I seen that have. Have you tried that before? I need to do it > two-way. But have not seen any special setup for that. Each site > will be entering data and all sites will need to see the updated > change. > > > On Fri, 01 Oct 2004 10:38:07 -0400, Michael Stassen > <[EMAIL PROTECTED]> wrote: > > Have you considered replication > > <http://dev.mysql.com/doc/mysql/en/Replication.html>? > > > > Michael > > > > > > > > spiv007 wrote: > > > > > I want to know what to best way to keep a 4 mysql servers sync. > > > > > > I have 4 remote locations, I am thinking about putting a mysql server > > > in each location and every hour have the 1 db that I need to sync to > > > sync together. > > > > > > Is there away to do its in somewhat real time or even a delay maybe of > > > an hour or two? > > > > > > > -- > 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: (if !update then insert) sequence - result Duplicate key :(
> Hi!
>
> Sorry for my ugly English.
> I use mysql in my PHP script(for calculating INs and OUTs from site).
> And I meet some strange thing there.
>
> Example goal: store statistics for clicks on some links.
> I use next algorithm:
>
> ---
> $key='somekeyvalue';
> dbquery("update sometable set count=count+1 where keyfield='$key'");
> if(mysql_affected_rows()<1)
> dbquery('insert into sometable set keyfield='$key', count=1');
> ---
>
> First question: this is good method? Or I MUST use pre-query with
> "select count(...) from sometable where keyfield='$key'" for detection:
> exists needle record(for update) or not(for insert)???
This method is fine. That is to say that I have seen it before in older
code. However, you might take alook at REPLACE:
http://dev.mysql.com/doc/mysql/en/REPLACE.html
And for MySQL 4.1.x you might take a look at INSERT ... ON DUPLICATE KEY
UPDATE
http://dev.mysql.com/doc/mysql/en/INSERT.html
>
> Second... My script work on many different hosts with different
> hardware, os(only Linux or FreeBSD), and different PHP and MySQL
> version. It works fine excepting one thing... Sometime happens errors
> like:
> MySQL: Duplicate entry 'somekeyvalue' for key 1, query was: insert into
> sometable set keyfield='somekeyvalue', count=1
I would look at your logic. What is $key? Where does it come from? If
`keyfield` is a unique index then you cannot have duplicate values.
>
> Why this happens? On some hosts this messageis very rare, but on others
> - so often... I cant understand reason :(
> My script use DB locks, so two clients cant make this situation(i
> think).
Do a little research to find out if your locking is working.
>
> Only one idea: sometime 'update' query dont work(or work, but dont
> return good result), and 'insert' query started... But WHY?
Again, I would look at your logic and try to use REPLACE or INSERT ... ON
DUPLICATE KEY UPDATE.
Regards,
Jim Grill
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT queries on replicate DB server
> > My question is: > Why would they deem it necessary to use yet a third server? Could it > be because the main server and the main slave are constantly being > updated and they wouldn't want to overload the main slave(which > is not on as high a horsepower of a box I know for a fact). Could it > be because maybe the subset of tables that they put on the third server > are relatively more stable and hence there arent so many writethroughs > so it can handle the complex selects better. > > All theories gladly accepted... > I'm not too sure about the third server either, but I do have an idea. It wouldn't make much sense if the third server had different data on it. That would tend to make things difficult to keep up to date - or maybe not. It might be a slave that they only connect to the master every so often. It's very common to have applications that write to one server and read from a slave server. Sometimes many slave servers since there are typically way more reads than writes. Perhaps they use the third server so that if the master or slave servers die there will always be a spare server for reads. As far as any difference in the tables on the third server... Since it is doing selects only you can start a slave server with a few options to speed things up like: --skip-innodb, --skip-bdb, --low-priority-updates, and --delay-key-write=ALL which will force the server to use non-transactional MyIsam tables for better performance. It's really tough to speculate. Every system administrator would probably do it a different way. Jim Grill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Conexiones no autorizadas
> > Hola , > > Estoy empezando a notar intentos de ingreso con usuarios ilegales > no existentes) a mi server. > > Mi pregunta es : Como lo rastreo teniendo como unico dato el numero de > ip desde el cual se esta conectando? > > O si alguien tiene una mejor idea o ha pasado por lo mismo, > le agradecere me comente sus experiencias > > Desde ya muchas gracias > Mauricio > > Hola Mauricio, MySQL List Espanol: http://lists.mysql.com/mysql-es Hadios Amigo :-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Addslashes / data sort
> PHP / MySQL
>
>
>
> I'm using PHP to retrieve user input and store it in MySQL. PHP's
> addslashes function is used on data going into the database and PHP's
> stripslashes function is being used on data coming from the database.
This
> is allowing me to store and retrieve data with no problems, but it's
causing
> problems when trying to sort data. In particular, data with double or
> single quotes is getting escaped, which changes it's position in the sort.
>
>
Sounds like you are escaping twice. If you have magic_quotes_gpc enabled
then your data is already being escaped when it's submitted. Most likely
this is your problem.
magic_quotes_gpc is a bad hack and is actually disabled in newer releases.
However, if you're a noob, you might want to leave it on. :-) The problem is
that when magic_quotes_gpc adds slashes to escape quotes and then you
explicitly escape them again with add_slashes() you end up with all quotes
escaped twice.
Now your asking: "why then is there only one slash in the database?"
Simple:
try this:
INSERT INTO sometable VALUES('Ed\'s','\'Pizza\'');
and then...
SELECT * FROM sometable;
+--+--+
| name | what |
+--+--+
| Ed's | 'Pizza' |
+--+--+
The slashes never actually get inserted into the table so there is no need
to call strip_slashes() on the results either. :-)
Jim Grill
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to pick the oldest row
> Hi , I'm trying to fetch the oldest row from a result > set in wich the resulting rows have duplicates, > > I need only one row for each duplicate. > > The problem is that I need always the oldest row > Like in the example > > SELECT * > FROM `mj_tmp` > WHERE idk NOT IN > (SELECT idk FROM `mj_tmp` > GROUP BY Concat(ida,u) > HAVING Count( Concat(ida,u) ) >1 ) > > I would like to get the first of this two rows > but I'm always getting the second. > > idk ida u d > 90 14450 13 2004-09-08 > 147 14450 13 2004-09-09 > > > Is there a way in which I can make sure I get always the oldest row? > Try adding something like ORDER BY d DESC LIMIT 1 Jim Grill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: high availability question
(Sorry for the long reply) > If i write to server A, will server B automatically be > notified of the change, and be properly updated? If i change server B > instead, will server A be notified and updated? Yes...Just like that. :-) They will both have the same data. I have a similar set up in my office. In fact, I have a two way replication of all my databases on two Linux machines and then I replicate only a few databases from one of the Linux machines on a windows machine for cross platform development purposes. > safe, a dirty hack or a > feature? Well. The thing is: the master server has no clue it is a master or how many slaves are present. So, in effect, a master can also be a slave and slaves can have slaves. This includes two-way replication. I'd say it's a feature and not a dirty hack. Safe? ...that depends. The only thing that is not safe is when you write to both machines at the same time. There is a possibility that updates can appear out of order at heavy load times even after all the changes have propagated both ways. That is to say that while you will have all your data in both databases but records may be in different orders if they were inserted or updated before propagation completed. This could be huge problem is the same record was updated on both servers or if you rely on LAST_INSERT_ID() to be accurate. There is a plethora of possible problems due to that fact. There are two scenarios: 1) For performance: Perform updates and inserts on the master and perform all reads from the slave. You can also replicate the master and slave on two more servers to add some redundancy. Two way replication is not necessary for this situation. If you know that you will read 70% of the time and write only 30% of the time you could use the master for INSERT, UPDATE, and DELETE and then use two slaves for reads. 2) For redundancy: Two way replication - both servers will be up to date and you can read or write to either server. In this scenario it is best to always read and write to *one server at a time* (the master) unless it dies - then switch to the slave while repairs are made. You can then STOP SLAVE on the slave and bring the master back up and replicate the slave from the mysql client prompt. When the master as back up to date STOP SLAVE on the master and START SLAVE on the slave again. Note that in both scenarios two-way replication is not really necessary. The only real benefit to having two way replication is if your a lazy bum like me and don't want to have to type anything or think to much when a server dies or you feel like switching server for some other reason. I am aware of scenarios where two-way replication is used along with special client code that will automatically connect to the slave if the master dies. This way if a database server crashes in the middle of the night the client code will simply redirect connections to the slave or back to the master if it somehow comes back to life (how lucky). I'm not too sure how safe this idea is and I'm sure that some unnecessary overhead is introduced by testing for live connections all the time. My best advice to you is to set up a few servers and start testing. Replication is not at all hard to accomplish. Best of luck. Jim Grill > Hi Jim, Hi all! > > Doing a two way replication, assures me that i can write and read data > from both servers? I mean, the app servers, can read from any data base > on demand. If i write to server A, will server B automatically be > notified of the change, and be properly updated? If i change server B > instead, will server A be notified and updated? > > For this to work, what MySQL version do i have to use? 4, 4.1, 5.0 > > I own both books by Jeremy and Paul, in Paul's book (the second edition, > BTW) I almost certainly understood that replication is one way only, > Master2Slave, the "cheat" ( just to give it a name! =) ) of configuring > Master and Slave in a two way relationship is safe, a dirty hack or a > feature? Paul's book is for MySQL 4, what version do i need for this to > work? > > I can hard code the app to query any of the two db, but i _MUST_ assure > that at any given time, they both have the same data. > > As always, thanxs! =) > > Vic. > > > > On Mon, 2004-09-27 at 12:43, Jim Grill wrote: > > > Hi guys! > > > > > > I am having some doubts in how to implement a high availability, > > > geographically distributed MySQL DB. > > > > > > The thing is, I would like to have.. let's say two (02) app. servers > > > with two (02) DB servers, running exactly the same data. In case one > > > fail, the other will be available. The thing is, how can i ensure that > > > both DB servers has exactly the same data? I took a look at Replication > > > features
Re: studying for the certification exam
> Hi, I took the MySQL core and professional certification exams and failed both, so now I want to go again, but does anyone have any tips or techniques for studying that they've found useful? > Wow, what a drag. Did you read the study guide? How long have you been a MySQL user? I am preparing to take the professional exam and have been reading the study guide. I find it to be very useful and informative in that it lets you know what percentage each topic will be covered in the exam. I also started reading the entire online manual. I skim through a lot of it and spend the most time on the areas that either interest me or are unclear. I have been a MySQL user since 1998. Most my experience was installation/administration for a web hosting provider until around 2001 at which time I began using it for my own projects. It's really only been the last year or two that I started feeling any kind of confidence with MySQL. Although I have not taken the exam, I am feeling pretty confident that I can pass after reading the online manual and the study guide from front to (almost) back. I hope I'm right about that feeling! Wish me luck :-) Best of luck to you, too. Jim Grill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: browser form question
> > I've been searching long and hard and have come across
> > a few techniques for changing text entries with lower
> > case (entered by web user) and changing them to have
> > first letter capitalized.
> >
> > in php. ucfirst
> > in java capitalize
> >
> > But I'm not having success on the database side.
> > Everything they enter from the web form hits the
> > database as entered, or raw, or lower cased.
> >
> > Is there a way to have the text fixed before it hits
> > the database?
>
> I'm a little confused. You're not entering data from the form directly
> into the database right?
>
> As long as there's a scripting language between the form and the database
> (perl, php, java, etc.) then you should (and easily can) handle the
> ucfirst right there.
>
> Sorry if that's thick headed...and if you mean something else, please
> elaborate.
>
>
If you absolutely must use MySQL to do this task:
INSERT INTO testing (name)
VALUES(CONCAT(UCASE(LEFT('jim',1)),SUBSTRING('jim',2)));
SELECT CONCAT(UCASE(LEFT(name,1)),SUBSTRING(name,2)) FROM testing;
:-)
There might be a better way to do this too??? I do agree that you should
probably handle this with simpler functions in your app like php's
ucfirst().
Jim Grill
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to change mysqldump output txt file format?
> Yeah. I am moving data from mysql server to sql
> server.
> Because I have single quote in some strings, it
> generated errors when I ran mysqldump scripts in sql
> server to import data in.
> i.e. strings Here's, Martin's ,... caused trouble.
> Does that mean sql didn't recognize escaped single
> quote?
> Thanks,
> Monet
>
I *think* sql server escapes single quotes *only* when two appear together.
So two single quotes in a row equals one single quote. Brilliant, eh? ...To
be a fly on the wall when that decision was made. :-) I think that is
actually the ANSI SQL standard for escaping single quotes and not just an M$
thing. It works in MySQL too by the way. Try it:
INSERT INTO testing values('Here''s','Martin''s','mom''s');
same as
INSERT INTO testing values('Here\'s','Martin\'s','mom\'s');
I suppose you could try to open the dump file in an editor like vi or
notepad or some other editor that has a search and replace and replace \'
with '' and see what happens. vi: :%s/\\'/''/g
The alternative would be to use mysqdump with -T option and use the
options --fields-terminated-by=, --fields-enclosed-by=, --fields-optionally-
enclosed-by=, --fields-escaped-by=, and --lines-terminated-by= to make a csv
file. You can use BCP or MS DTS to load a csv into sql server tables. There
may be another sql server equivelant to LOAD DATA INFILE that can accept csv
or tsv. Who knows???
Good luck,
Jim Grill
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to change mysqldump output txt file format?
> Is there nothing you can do with > --fields-terminated-by=... > --fields-enclosed-by=... > --fields-optionally-enclosed-by=... > --fields-escaped-by=... > --lines-terminated-by=... As I mentioned, those options **only** apply when using the "-T" option which creates a tab delimited dump file. That would be a cool option though. :-) There are a number programs that can assist with converting to and from MySQL if that's the goal. Regards, Jim Grill - Original Message - From: [EMAIL PROTECTED] To: Jim Grill Cc: Monet ; mysql Sent: Friday, September 24, 2004 1:59 PM Subject: Re: how to change mysqldump output txt file format? Jim - He needs to change the format because he isn't exporting from one MySQL database to another His destination database doesn't like the escaped single quotes. Here is the manual page for mysqldump: http://dev.mysql.com/doc/mysql/en/mysqldump.html Is there nothing you can do with --fields-terminated-by=... --fields-enclosed-by=... --fields-optionally-enclosed-by=... --fields-escaped-by=... --lines-terminated-by=... to get what you want from mysqldump? Shawn Green Database Administrator Unimin Corporation - Spruce Pine "Jim Grill" <[EMAIL PROTECTED]> wrote on 09/24/2004 02:35:40 PM: > > Hello everyone, > > > > In mysqldump output txt file, all datetime, varchar, > > text fields value are surrounding by single quotes. Is > > there any way that the single quotes can be replaced > > by double quotes in the txt file? > > Furthermore, if you have a single quote in text field, > > it will automatically replaced by \'. But I like to > > keep it in the way it input. > > > > There is no way to change the use of single quotes in a standard dump. The > only time you have a choice is when you use the -T option, which creates a > tab or csv type dump files depending on options present on the command line. > You could use this option if you wanted. The only draw back is that > mysqdump -T must be run on the same machine as the server. > > However, why is the presents of escaped single quotes a problem? They are > only escaped to let MySQL know to treat them as literal single quotes and > not string delimiters. They do not actually get inserted into your table > with the slashes. > > Regards, > > Jim Grill > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] >
Re: how to change mysqldump output txt file format?
> Hello everyone, > > In mysqldump output txt file, all datetime, varchar, > text fields value are surrounding by single quotes. Is > there any way that the single quotes can be replaced > by double quotes in the txt file? > Furthermore, if you have a single quote in text field, > it will automatically replaced by \'. But I like to > keep it in the way it input. > There is no way to change the use of single quotes in a standard dump. The only time you have a choice is when you use the -T option, which creates a tab or csv type dump files depending on options present on the command line. You could use this option if you wanted. The only draw back is that mysqdump -T must be run on the same machine as the server. However, why is the presents of escaped single quotes a problem? They are only escaped to let MySQL know to treat them as literal single quotes and not string delimiters. They do not actually get inserted into your table with the slashes. Regards, Jim Grill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [OFF-TOPIC] MySQL License Question
> Hi! > > I sent this question to the mysql license email, but it's been more than > 3 days since then and I haven't received a answer... So I will ask it > again here, to see if someone can clarify this issue. > Are you actually distributing MySQL with your application? Or are you just using installations that are installed separately? You only need the licensed version for two possible reasons: 1) You are bundling the MySQL server or the C client code in your application which is not licensed under the GPL or a compatible Open Source Initiative license approved by MySQL. 2) You want support. That's it. Pretty simple. If your application simply *uses* MySQL but you are not distributing any of the MySQL source code in your program or along side your program you don't need the license. If I'm wrong about this someone please yell at me. I've been studying for the MySQL certification and this is covered in the first chapter of the study guide. :-) Regards Jim Grill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: formating output
> Hi folks - > I have looked thru the documentation and the books I have on MySQL - > but have not found anything on how to format column output from select > statements. I'm trying to do several things - > a) if a date is the default of -00-00 then just show a blank > b) for a phone number of 99, format the output as 999-999- > Hints? Here are some hints. :-) a) http://dev.mysql.com/doc/mysql/en/Control_flow_functions.html b) http://dev.mysql.com/doc/mysql/en/String_functions.html Regards, Jim Grill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with tables;
> Hi, > > I'm trying to create a table using the commands below, and everything seems > to work and the table is there - I just can's access any of the fields. This > happens on both my Windows and Linux versions of MySQL. Any help is > appreciated. Note, I have 8 other databases that I created with no problem > about 2 months ago (on both platforms) - this is the first time I have tried > since then. Any help is appreciated > > Thanks > > I am in the proper database when I run the commands below... > > CREATE TABLE spamdata ( > id bigint(20) NOT NULL, > dates varchar(60) NOT NULL, > probe varchar(100) NOT NULL, > ip varchar(15) NOT NULL, > fqdnvarchar(100) NOT NULL, > reason varchar(100) NOT NULL, > portvarchar(2) NOT NULL, > codevarchar(10) NOT NULL, > PRIMARY KEY (idm)); > > Query OK, 0 rows affected (0.08 sec) *** This tells me nothing happened... > > When I run 'desc id' (or any of the others) I get the following: > > mysql> desc id; > ERROR 1146: Table 'spamtool.id' doesn't exist > > This shows the table is there though. > > mysql> show tables; > ++ > | Tables_in_spamtool | > ++ > | spamdata | > ++ > 1 rows in set (0.00 sec) > Try doing 'desc spamdata' There is no table named id; hence the error. Regards, Jim Grill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql still can't start up OT
> I compiled mysql w/o this option. Is there any file that I can modify and > bounce the server to take this? I modified: > > my-path/share/mysql > > -rw-r--r-- 1 root other 4938 Jul 23 10:32 my-huge.cnf > -rw-r--r-- 1 root other 4914 Jul 23 10:33 my-large.cnf > -rw-r--r-- 1 root other 4897 Jul 23 10:33 my-medium.cnf > -rw-r--r-- 1 root other 2482 Jul 23 10:34 my-small.cnf > > where > > socket = /tmp/mysql.sock > > to > > socket = /software/mysql-4.0.14/tmp/mysql.sock > > and bounced the server, but it still created /tmp/mysql.sock > > There might be another config file the server reads when it started, that > I didn't modified, but I don't which? > > > Thanks! > Annie > Well None of the files you edited are actually in use. :-) You should have picked on of those cnf files and copied it to your sysconfigdir specified by ./configure --sysconfdir=/path Typically, the sysconfig directory is /etc so you'll want to copy on of those example cnf files like "my-medium.cnf" to /etc/my.cnf http://dev.mysql.com/doc/mysql/en/Option_files.html You can change the location of mysql.sock a few different ways. 1) Add "socket=/path/to/mysql.sock" without the quotes under the [mysqld] and [client] sections of your my.cnf file. 2) Specify it when starting the server like mysqld_safe --socket=/path/to/mysql.sock 3) Reconfigure mysql adding --with-unix-socket-path=/path/to/mysql.sock Regards, Jim Grill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql still can't start up OT
> Another question related to server start: > > Can I configure mysql.sock to some other dir, since /tmp/mysql.sock might > easiely being deleted by someone? > > > Thanks! > Annie > > Yes, you can - and I'd make that recommendation on shared systems. configure option: --with-unix-socket-path=/path/to/mysql.sock Make sure that the directory is owned (or at least writable) by the user that MySQL runs as or you will get the dreaded "MySQL ended" errors when starting the server. Regards, Jim Grill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.1.4a-gamma in production?
> Hello, > > >>Is gamme the last step > >>until the 4.1.4 will be released? > > > > Yes. > > > >>Or will rc's follow before? > > > > No. > > that's nice to hear. So when will the final version be released? 2 days? > 2 weeks? > > Regards > Marten > Wishful thinking :-) As far as I'm aware there is no set time frame from Gamma to Production. However, Gamma status indicates that no major bugs have been found for over a month. IMHO: With an estimated (and growing) user base of over 4 million (early 2003) I'd say that Gamma is more than safe enough to use in a production environment. Regards, Jim Grill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql and PHP
> People, > > This more of a php mysql question. I have installed PHP ( 5.01) with > Apache(1.31) and it runs. > I have install Mysql (the latest as of last night) and it runs. My > problem is that PHP does not see > Mysql. Now I have done this installation 4 or 5 times but th elast time > was over a year ago. > > Any ideas? > > Thanks! > > Nestor :-) > http://www.IneedWork.org > How about some more info. Did you install PHP from source or an RPM? Did you configure PHP --with-mysql or --with-mysqli? Did you install MySQL after you installed PHP and Apache or before? Regards Jim Grill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql still can't start up
http://www.catb.org/~esr/faqs/smart-questions.html Stop repeating the same question or you will never get an answer. Try again please. > Hi > > > I have installed some software on aix5.2 . > the list of software: > apache-1.3.29-1.aix4.3.ppc.rpm > php-4.0.6-5.aix4.3.ppc.rpm > MySQL-3.23.47-3.aix4.3.ppc.rpm > MySQL-client-3.23.47-3.aix4.3.ppc.rpm > > All of them was installed by using the command : rpm -i *.* > then > > Apache and php can work > but mysql can't run > when I used the command "safe_mysqld start" the system told me " > Starting mysqld daemon with databases from /var/lib/mysql > 040913 19:08:45 mysqld ended" > when I used the command "mysql" the system told me > " > ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)" > Many friends tell me to see the err log, the below is the err log(locaohost.err): > > 040915 19:14:06 mysqld started > /opt/freeware/libexec/mysqld: unrecognized option `--key_buffer=16M' > /opt/freeware/libexec/mysqld Ver 3.23.58 for ibm-aix5.1.0.0 on powerpc > Copyright (C) 2000 MySQL AB, by Monty and others > This software comes with ABSOLUTELY NO WARRANTY. This is free software, > and you are welcome to modify and redistribute it under the GPL license > Starts the MySQL server > > Usage: /opt/freeware/libexec/mysqld [OPTIONS] > > --ansi Use ANSI SQL syntax instead of MySQL syntax > -b, --basedir=path Path to installation directory. All paths are > usually resolved relative to this > --big-tables Allow big result sets by saving all temporary sets > on file (Solves most 'table full' errors) > --bind-address=IP Ip address to bind to > --bootstrap Used by mysql installation scripts > --character-sets-dir=... > Directory where character sets are > --chroot=path Chroot mysqld daemon during startup > --core-file Write core on errors > -h, --datadir=path Path to the database root > --default-character-set=charset > Set the default character set > --default-table-type=type > Set the default table type for tables > --delay-key-write-for-all-tables > Don't flush key buffers between writes for any MyISAM > table > --enable-locking Enable system locking > -T, --exit-info Used for debugging; Use at your own risk! > --flush Flush tables to disk between SQL commands > -?, --help Display this help and exit > --init-file=file Read SQL commands from this file at startup > -L, --language=... Client error messages in given language. May be > given as a full path > --local-infile=[1|0] Enable/disable LOAD DATA LOCAL INFILE > -l, --log[=file] Log connections and queries to file > --log-bin[=file] Log queries in new binary format (for replication) > --log-bin-index=file File that holds the names for last binary log files > --log-update[=file] Log updates to file.# where # is a unique number > if not given. > --log-isam[=file] Log all MyISAM changes to file > --log-long-format Log some extra information to update log > --low-priority-updates INSERT/DELETE/UPDATE has lower priority than selects > --log-slow-queries=[file] > Log slow queries to this log file. Defaults logging > to hostname-slow.log > --pid-file=path Pid file used by safe_mysqld > --myisam-recover[=option[,option...]] where options is one of DEAULT, > BACKUP or FORCE. > --memlock Lock mysqld in memory > -n, --new Use very new possible 'unsafe' functions > -o, --old-protocol Use the old (3.20) protocol > -P, --port=... Port number to use for connection > > -O, --set-variable var=option > Give a variable an value. --help lists variables > -Sg, --skip-grant-tables > Start without grant tables. This gives all users > FULL ACCESS to all tables! > --safe-mode Skip some optimize stages (for testing) > --safe-show-database Don't show databases for which the user has no > privileges > --safe-user-create Don't new users cretaion without privileges to the > mysql.user table > --skip-concurrent-insert > Don't use concurrent insert with MyISAM > --skip-delay-key-write > Ignore the delay_key_write option for all tables > --skip-host-cache Don't cache host names > --skip-locking Don't use system locking. To use isamchk one has > to shut down the server. > --skip-name-resolve Don't resolve hostnames. > All hostnames are IP's or 'localhost' > --skip-networking Don't allow connection with TCP/IP. > --skip-new Don't use new, possible wrong routines. > > --skip-stack-traceDon't print a stack trace on failure > --skip-show-database Don't allow 'SHOW DATABASE' commands > --skip-thread-priority > Don't give threads different priorities. > --socket=... Socket file to use for connection > -t, --tmpdir=path Path for temporary files > --sql-mode=option[,option[,option...]] where option can be one of: >
Re: question
> Hi > > > I have installed some software on aix5.2 . > the list of software: > apache-1.3.29-1.aix4.3.ppc.rpm > php-4.0.6-5.aix4.3.ppc.rpm > MySQL-3.23.47-3.aix4.3.ppc.rpm > MySQL-client-3.23.47-3.aix4.3.ppc.rpm > > All of them was installed by using the command : rpm -i *.* > then > > Apache and php can work > but mysql can't run > when I used the command "safe_mysqld start" the system told me " > Starting mysqld daemon with databases from /var/lib/mysql > 040913 19:08:45 mysqld ended" > when I used the command "mysql" the system told me > " > ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)" > > > help me please > > thanks a lot > Yours >LiRui > [EMAIL PROTECTED] > 2004-09-14 > Sounds like a permissions problem. Make sure the data directory is owned by the user that mysqld runs under. Usually `chown -R mysql /var/lib/mysql` will do the trick. The error: "Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)" is due to the fact that mysqld is not running when you try to connect via the mysql client. Jim Grill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: when to use backquote in SQL
> In the last episode (Sep 09), leegold said: > > Could anyone link me or explain the purposes of backquotes in an SQL > > statement. I tried searching the manual and googling it but couldn't > > find a simple explaination. ``` vs. "regular" single quotes'''. > > Thanks, Lee G. > > Backquotes are used to delimit table or field names; they aren't used > to delimit SQL strings the way ' or " are. You'll almost never need to > use them unless you have spaces or other strange characters in your > table/field names. > > -- > Dan Nelson > [EMAIL PROTECTED] > Backtics can also be useful to avoid SQL injections if an application your working on requires table names or field names to be supplied from user input (always a bad idea) like a select box. Jim Grill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help urgent please
> > -- > help please > > please tell me how to extract a script file of a database from mysql commandprompt. > > s.deepak > > > This life is a hard fact; work your way through it boldly, though it may be adamantine; no matter, the soul is stronger > > Swami Vivekananda > Are you looking for mysqldump?? If you want to produce a dump file of a table: mysqldump -u yourusername -p --add-drop-table dbname tablename > tablename.sql to do the whole database: mysqldump -u yourusername -p --add-drop-table dbname > dbname.sql also do "man mysqldump" or see http://dev.mysql.com/doc/mysql/en/mysqldump.html The "--add-drop-table" will add a "DROP TABLE IF EXISTS tablename" to your script before creating and populating the tables. This is useful when restoring a possibly corrupt table. Jim Grill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQL CONNECT ISSUE
> I have been getting following error. > [polaris] ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) > > How to fix that? > What is default port for mysqlserver? > thx > -seena > Is this a new install? Have you set the password yet? Have you forgotten the password? The default port is 3306 Jim Grill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Compilation Error
> Hi, > > I am trying to compile MySQL. I know that it is possible to install this > as a binary, that is not my goal. > > My environment consists of the following: If there are other tools that > I need to specify please let me know. > gcc-3.2-7 > libgcc-3.2-7 > gcc-c++-3.2-7 > > I run configure as specified at the MySQL site: From config.log: > >./configure --prefix=/usr/local/mysql --with-extra-charset=complex > --enable-thread-safe-client --enable-local-infile --enable-assembler > --disable-shared --with-client-ldflags=-all-static > --with-mysqld-ldflags=-all-static > > > When I run make I receive the following output: > > libmysql.c:1850: warning: passing arg 5 of `gethostbyname_r' from > incompatibleointer type > libmysql.c:1850: too few arguments to function `gethostbyname_r' > libmysql.c:1850: warning: assignment makes pointer from integer without > a castmake[2]: *** [libmysql.lo] Error 1 > > I have googled this error message and the result include the following > recommendation from MySQL: > > "This is known problem with RedHat. In order to build MySQL you need to > have g++ > installed from separate RPM > > In RedHat 8.0 RPM in quesiton is gcc-c++-3.2-7.i386.rpm" > > It appears that I have installed gcc-c++-3.2-7.i386.rpm as you can see > above from my build environment obtained by: > > rpm -qa | grep gcc > > Any help or pointers would be greatly appreciated. > > Andrew > Looks like you have the right stuff. Have you tried the following: (run "make distclean" before configuring again to be sure you get a clean start.) CFLAGS="-O3 -mcpu=pentiumpro" CXX=gcc CXXFLAGS="-O3 -mcpu=pentiumpro \ -felide-constructors -fno-exceptions -fno-rtti" \ ./configure --your options here change "-mcpu=pentiumpro" to suite your system (i386, i486, i586, i686, pentium, pentiumpro, k6, or athlon). Jim Grill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sql problem
> Now, to complicate things further, suppose there is > yet another table (grandparent, let's say) and there is > a column in parent that is grandparent_id. I only want > results where parent.grandparent_id = 'Fred'. How do > I do that? Is it with a WHERE clause or some other > SQL magic? > > Dean > This is not meant as an "rtfm", but this helped me in the past. http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins/ Jim Grill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sql problem
> Suppose I have two tables: parent and > child. I want to create an SQL statement > that will get the count of all children > belonging to parent. Is this possible in > one statement? The one I have works > if parent has children, but not if the > number of children is 0. Here's what > I have: > > SELECT parent.id,parent.name,COUNT(child.parent_id) > FROM PARENT INNER JOIN CHILD ON parent.id=child.parent_id > GROUP BY parent.id; > > Any ideas? > Thanks. > Dean Hoover > This is untested, but I've used this same technique for a similar problem. The only part that I am unsure of is using COUNT inside an IF. SELECT parent.id, parent.name, IF(child.parent_id IS NULL, 'none', COUNT(child.parent_id)) AS child_count FROM PARENT INNER JOIN CHILD ON parent.id=child.parent_id GROUP BY parent.id; Jim Grill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table fixed BUT...Re: Table crashed! Please help OT
> Bingo. Yeah, that works very well. > Really appreciated. > > BTW, still curious about the reason of duplicate.:) > Any thoughts? > > Monet > Several thoughts; one of which is "Thank my lucky stars! I have my data back!" :-) Other, less significant thoughts include the possibility that some esoteric communication problems between Navicat and MySQL caused the problem to begin with, which brings me back to my first thought. Best of luck to you, Jim Grill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table fixed BUT...Re: Table crashed! Please help
> Hello there, > > I tried "REPAIR TABLE" to recovery the crushed temp > table and that works. Thanks a lot. > > But, after reparation, there are some duplicated > records generated. Is that because the temp table has > no primary key? Or is because the table was crushed in > the middle of query? > Anyway, I tried to delete the duplicated records. BUT, > I can't. if I just simply delete one of duplicate > record, then another one is deleted as well. For > instance, there are two duplicate record with qid = > 3170, I delete one of them from table, confirm the > delete, refresh table, another one disappeared too. > Does anyone has any idea about what happened and what > should I do to delete duplicate records? > > Many thanks for any advise. > Monet > Try: SELECT COUNT(*) FROM table WHERE id=[your duplicate value]; This will show you how many dups there are... so let's say there were 7... DELETE FROM table WHERE id=[your duplicate value] LIMIT 6; Now you have one left. I hope that helps. Jim Grill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
