RE: Speeding Up Process
For Each is always best avoided, think set based! Might be better if you could forward your SQL. Rhys -Original Message- From: Neil Tompkins [mailto:[EMAIL PROTECTED] Sent: 04 October 2007 12:19 To: My SQL Subject: Speeding Up Process Hi I've the following process/queries which I want to speed up, for a product inventory/warehouse which we have a number of items available to sell each day For Each Company For Each Product For Next 7 Days CheckQuantityUpdateQuantity Next Day Next Product Next Company Is it possible to re-write this routine so that it executes quicker ? Thanks,Neil _ Get free emoticon packs and customisation from Windows Live. http://www.pimpmylive.co.uk This email is confidential and may also be privileged. If you are not the intended recipient please notify us immediately by telephoning +44 (0)20 7452 5300 or email [EMAIL PROTECTED] You should not copy it or use it for any purpose nor disclose its contents to any other person. Touch Local cannot accept liability for statements made which are clearly the sender's own and are not made on behalf of the firm. Touch Local Limited Registered Number: 2885607 VAT Number: GB896112114 Cardinal Tower, 12 Farringdon Road, London EC1M 3NN +44 (0)20 7452 5300 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: PROBLEM
Not quite what you're after but you might like to use... http://dev.mysql.com/doc/refman/5.0/en/safe-updates.html -Original Message- From: Krishna Chandra Prajapati [mailto:[EMAIL PROTECTED] Sent: 13 September 2007 15:30 To: MySql Subject: PROBLEM Hi All, There are many user accessing the production server and making a lots of mistake, running wrong query (It occurs by mistake if they don't pay attention) . There is any tools or script which can set autocommit=0; whenever any user logged in. OR done some thing wrong which can be revert back. Regards, Krishna This email is confidential and may also be privileged. If you are not the intended recipient please notify us immediately by telephoning +44 (0)20 7452 5300 or email [EMAIL PROTECTED] You should not copy it or use it for any purpose nor disclose its contents to any other person. Touch Local cannot accept liability for statements made which are clearly the sender's own and are not made on behalf of the firm. Touch Local Limited Registered Number: 2885607 VAT Number: GB896112114 Cardinal Tower, 12 Farringdon Road, London EC1M 3NN +44 (0)20 7452 5300 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Column type suggestions
SELECT * FROM tablename PROCEDURE ANALYSE() Anyone know of a way to get the size of a row in bytes? -Original Message- From: Benjamin Wiechman [mailto:[EMAIL PROTECTED] Sent: 24 August 2007 16:18 To: mysql@lists.mysql.com Subject: Column type suggestions I recall there is a command that will display suggested optimal columns types for a given table based on the data stored in the table, but I haven't been able to figure out what it is. Help! Thanks. Ben Wiechman Wisper High Speed Internet [EMAIL PROTECTED] This email is confidential and may also be privileged. If you are not the intended recipient please notify us immediately by telephoning +44 (0)20 7452 5300 or email [EMAIL PROTECTED] You should not copy it or use it for any purpose nor disclose its contents to any other person. Touch Local cannot accept liability for statements made which are clearly the sender's own and are not made on behalf of the firm. Touch Local Limited Registered Number: 2885607 VAT Number: GB896112114 Cardinal Tower, 12 Farringdon Road, London EC1M 3NN +44 (0)20 7452 5300 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Why is the average of an int column returned as a string
I'd guess this may be a funny related to Crystal Reports as I don't recall experiencing this myself. Rhys -Original Message- From: Eric Lommatsch [mailto:[EMAIL PROTECTED] Sent: 22 August 2007 22:21 To: Rhys Campbell; mysql@lists.mysql.com Subject: RE: Why is the average of an int column returned as a string I have tried that and that seems to be working for me. I am just wondering why all of the sudden queries that were returning the averages as numeric values as string values. Thank you for your suggestion. Thank you Eric H. Lommatsch Programmer 360 Business 2087 South Grant Street Denver, CO 80210 Tel 303-777-8939 Fax 303-778-0378 [EMAIL PROTECTED] -Original Message- From: Rhys Campbell [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 22, 2007 3:34 AM To: Eric Lommatsch; mysql@lists.mysql.com Subject: RE: Why is the average of an int column returned as a string You could use the CAST function, although I have just discovered that MySQL is rather limited in the types you are able to CAST to... http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html -Original Message- From: Eric Lommatsch [mailto:[EMAIL PROTECTED] Sent: 21 August 2007 22:52 To: mysql@lists.mysql.com Subject: Why is the average of an int column returned as a string Hello, I am working with a query that calculates the averages of survey answers. The survey answers are stored in the database as int(11) fields. When I run the query the results that I am getting are being returned as string data. The query that I am working with is a data source for a Crystal Reports reports. The average columns that are being returned by the query are used in the report in fields that have been formatted for double values. I am using MySQL 5.0.18 as the database. What would I have to do to get the averages of Int columns to return as doubles, rather then having to change all of my columns to be double columns? Thank you Eric H. Lommatsch Programmer 360 Business 2087 South Grant Street Denver, CO 80210 Tel 303-777-8939 Fax 303-778-0378 [EMAIL PROTECTED] This email is confidential and may also be privileged. If you are not the intended recipient please notify us immediately by telephoning +44 (0)20 7452 5300 or email [EMAIL PROTECTED] You should not copy it or use it for any purpose nor disclose its contents to any other person. Touch Local cannot accept liability for statements made which are clearly the sender's own and are not made on behalf of the firm. Touch Local Limited Registered Number: 2885607 VAT Number: GB896112114 Cardinal Tower, 12 Farringdon Road, London EC1M 3NN +44 (0)20 7452 5300 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Why is the average of an int column returned as a string
You could use the CAST function, although I have just discovered that MySQL is rather limited in the types you are able to CAST to... http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html -Original Message- From: Eric Lommatsch [mailto:[EMAIL PROTECTED] Sent: 21 August 2007 22:52 To: mysql@lists.mysql.com Subject: Why is the average of an int column returned as a string Hello, I am working with a query that calculates the averages of survey answers. The survey answers are stored in the database as int(11) fields. When I run the query the results that I am getting are being returned as string data. The query that I am working with is a data source for a Crystal Reports reports. The average columns that are being returned by the query are used in the report in fields that have been formatted for double values. I am using MySQL 5.0.18 as the database. What would I have to do to get the averages of Int columns to return as doubles, rather then having to change all of my columns to be double columns? Thank you Eric H. Lommatsch Programmer 360 Business 2087 South Grant Street Denver, CO 80210 Tel 303-777-8939 Fax 303-778-0378 [EMAIL PROTECTED] This email is confidential and may also be privileged. If you are not the intended recipient please notify us immediately by telephoning +44 (0)20 7452 5300 or email [EMAIL PROTECTED] You should not copy it or use it for any purpose nor disclose its contents to any other person. Touch Local cannot accept liability for statements made which are clearly the sender's own and are not made on behalf of the firm. Touch Local Limited Registered Number: 2885607 VAT Number: GB896112114 Cardinal Tower, 12 Farringdon Road, London EC1M 3NN +44 (0)20 7452 5300 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with a complex query
I solved a similar problem with a messaging system that was very slow because it was doing full table scan each time the query ran. I didn't have the chance to change the schema so this is what I came up with... SET @var = (SELECT MAX(message_id) FROM messages); SELECT columns... FROM message WHERE your cirteria = blah AND message_id BETWEEN @var - 1 AND @var This approach allowed me to elimiate the full table scan as we were only scanning the most recent 10, 000 rows. This assumes that everything you want falls within this range. It worked for us and maybe it will for you. -Original Message- From: Hugo Ferreira da Silva [ mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] ] Sent: 14 August 2007 19:19 To: mysql@lists.mysql.com Subject: Re: Problem with a complex query I've created an index with the statement create index `usuario_pasta_situacao` on `mensagens` (codusuario, codpasta, situacao); And for mensagenspara table this index create index `mensagem_usuario_pasta_situacao_idx` on `mensagenspara` (codmensagem, codusuario, codpasta, situacao); This is query used: SELECT m.codmensagem, m.codprioridade, m.codusuario, m.codmensagemoriginal, m.codmensagempai, m.assunto, m.dataenvio, m.horaenvio, m.datalimite, m.horalimite, m.anexo, m.tipo, u.nome, up.nome as nomepara, mp.codrespondida, mp.codmensagempara, mp.codusuario as codusuariopara, mp.situacao FROM mensagens m, mensagenspara mp, usuarios u, usuarios up WHERE m.ativo=1 AND m.codmensagem=mp.codmensagem AND m.codusuario=u.codusuario AND mp.codusuario=up.codusuario AND ( ( m.codpasta=1 AND m.codusuario = 916 AND m.situacao 0 ) OR ( mp.codpasta=1 AND mp.codusuario = 916 AND mp.situacao 4 ) ) GROUP BY m.codmensagem ORDER BY m.dataenvio DESC, m.horaenvio DESC, m.codmensagem DESC LIMIT 0,20 And this is what explain shows: id: 1 select_type: SIMPLE table: m type: ALL possible_keys: PRIMARY,usuario_pasta_situacao key: key_len: ref: rows: 68337 Extra: Using where; Using temporary; Using filesort --- id: 1 select_type: SIMPLE table: u type: eq_ref possible_keys: PRIMARY,codusuario key: PRIMARY key_len: 4 ref: teste2.m.codusuario rows: 1 Extra: --- id: 1 select_type: SIMPLE table: mp type: ref possible_keys: mensagem_usuario_pasta_situacao_idx key: mensagem_usuario_pasta_situacao_idx key_len: 5 ref: teste2.m.codmensagem rows: 3 Extra: Using where --- id: 1 select_type: SIMPLE table: up type: eq_ref possible_keys: PRIMARY,codusuario key: PRIMARY key_len: 4 ref: teste2.mp.codusuario rows: 1 Extra: --- I'm a little consufed with these indexes... could you please suggest a better way to create them? And again, thank you for your attention and patience :-) This email is confidential and may also be privileged. If you are not the intended recipient please notify us immediately by telephoning +44 (0)20 7452 5300 or email [EMAIL PROTECTED] You should not copy it or use it for any purpose nor disclose its contents to any other person. Touch Local cannot accept liability for statements made which are clearly the sender's own and are not made on behalf of the firm. Touch Local Limited Registered Number: 2885607 VAT Number: GB896112114 Cardinal Tower, 12 Farringdon Road, London EC1M 3NN +44 (0)20 7452 5300
RE: Problem with a complex query
Does the other user_id have a lot of messages. I think MySQL will choose to table ignore the index if the retrieved rows are above 30% of the table total. Have you tried FORCE INDEX? -Original Message- From: Hugo Ferreira da Silva [mailto:[EMAIL PROTECTED] Sent: 15 August 2007 13:35 To: mysql@lists.mysql.com Subject: Re: Problem with a complex query I found something weird. This is my query now -- (SELECT m.codmensagem, m.codprioridade, m.codusuario, m.codmensagemoriginal, m.codmensagempai, m.assunto, m.dataenvio, m.horaenvio, m.datalimite, m.horalimite, m.anexo, m.tipo, u.nome, up.nome as nomepara, mp.codrespondida, mp.codmensagempara, mp.codusuario as codusuariopara, mp.situacao FROM mensagenspara mp, mensagens m, usuarios u, usuarios up WHERE m.ativo=1 AND mp.codmensagem = m.codmensagem AND u.codusuario = m.codusuario AND up.codusuario = mp.codusuario AND mp.codpasta = 2 AND mp.situacao != 4 AND mp.codusuario = USER_CODE) UNION (SELECT m.codmensagem, m.codprioridade, m.codusuario, m.codmensagemoriginal, m.codmensagempai, m.assunto, m.dataenvio, m.horaenvio, m.datalimite, m.horalimite, m.anexo, m.tipo, u.nome, up.nome as nomepara, mp.codrespondida, mp.codmensagempara, mp.codusuario as codusuariopara, mp.situacao FROM mensagens m,mensagenspara mp, usuarios u, usuarios up WHERE m.ativo=1 AND mp.codmensagem = m.codmensagem AND u.codusuario = m.codusuario AND up.codusuario = mp.codusuario AND m.codpasta = 2 AND m.situacao = 1 AND m.codusuario = USER_CODE GROUP BY m.codmensagem ) ORDER BY dataenvio DESC, horaenvio DESC, codmensagem DESC LIMIT 0,40 --- But depending on what USER_CODE I use, it found or not and index. For example, if I use my code, 916: id: 2 select_type: UNION table: mp type: ref possible_keys: usuario_mensagem_situacao key: usuario_mensagem_situacao key_len: 10 ref: teste2.up.codusuario,teste2.m.codmensagem rows: 1 Extra: Using where But if I use any other code: id: 2 select_type: UNION table: mp type: ALL possible_keys: usuario_mensagem_situacao key: key_len: ref: rows: 197980 Extra: Someone could explain this behavior and how I can fix? Thanks in advance. This email is confidential and may also be privileged. If you are not the intended recipient please notify us immediately by telephoning +44 (0)20 7452 5300 or email [EMAIL PROTECTED] You should not copy it or use it for any purpose nor disclose its contents to any other person. Touch Local cannot accept liability for statements made which are clearly the sender's own and are not made on behalf of the firm. Touch Local Limited Registered Number: 2885607 VAT Number: GB896112114 Cardinal Tower, 12 Farringdon Road, London EC1M 3NN +44 (0)20 7452 5300 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with a complex query
Struggling with the Portuguese here but... What kind of indexes do you have in place? Are the y appropriate? I have had some success with removed or clauses from queries, creating a new query and join them with a UNION ALL. -Original Message- From: Hugo Ferreira da Silva [mailto:[EMAIL PROTECTED] Sent: 14 August 2007 16:04 To: mysql@lists.mysql.com Subject: Problem with a complex query Hi, I have a message system wich work in this way: - Each message can be sent to one or more recipients - Each message received have a lot of settings, like date and hour received, date and hour of read, mark as read and so on - When the user select view message, the system will get the history for this message, with each answers of that message. The first table, messages, has about 56.000 rows, and the messages for table has about 200.000 rows. The problem is when the user goes to messages list. Because the message could be moved to any folder, I have to query in messages table and messages for table, to check if in the selected folder exists messages sent or received. Below is the tables structures of my system. Users table (called usuarios, in portuguese) CREATE TABLE `usuarios` ( `codusuario` int(11) NOT NULL AUTO_INCREMENT, `codexterno` varchar(11) DEFAULT NULL, `codgrupo` smallint(6) DEFAULT NULL, `nome` varchar(50) DEFAULT NULL, `login` varchar(50) DEFAULT NULL, `senha` varchar(50) DEFAULT NULL, `senhacriptografada` varchar(50) DEFAULT NULL, `tamanhoarquivos` int(11) DEFAULT NULL, `cronos` int(11) DEFAULT NULL, `email` varchar(100) DEFAULT NULL, `ativo` int(11) DEFAULT NULL, PRIMARY KEY (`codusuario`), UNIQUE KEY `codusuario` (`codusuario`) ) ENGINE=MyISAM AUTO_INCREMENT=931 DEFAULT CHARSET=latin1 Messages table (mensagens) CREATE TABLE `mensagens` ( `codmensagem` int(11) NOT NULL DEFAULT '0', `codprioridade` int(11) DEFAULT NULL, `assunto` varchar(150) DEFAULT NULL, `assuntoconsulta` varchar(150) DEFAULT NULL, `msg` text, `codusuario` int(11) DEFAULT NULL, `dataenvio` int(11) DEFAULT NULL, `horaenvio` int(11) DEFAULT NULL, `situacao` int(11) DEFAULT NULL, `datalimite` int(11) DEFAULT NULL, `horalimite` int(11) DEFAULT NULL, `anexo` int(11) DEFAULT NULL, `codmensagemoriginal` int(11) DEFAULT NULL, `codmensagempai` int(11) DEFAULT NULL, `codpasta` int(11) DEFAULT NULL, `tipo` int(11) DEFAULT NULL, `dhdatatarefa` int(11) DEFAULT NULL, `dhhoratarefa` int(11) DEFAULT NULL, `repvalor` int(11) DEFAULT NULL, `repperiodo` int(11) DEFAULT NULL, `repnvezes` int(11) DEFAULT NULL, `repdataterminar` int(11) DEFAULT NULL, `avvalor` int(11) DEFAULT NULL, `avtipo` int(11) DEFAULT NULL, `avdata` int(11) DEFAULT NULL, `avhora` int(11) DEFAULT NULL, `alarmetipo` int(11) DEFAULT NULL, `alarmevalor` int(11) DEFAULT NULL, `ativo` int(11) DEFAULT NULL, `permiteresposta` int(11) DEFAULT NULL, PRIMARY KEY (`codmensagem`), KEY `codusuario` (`codusuario`,`codpasta`,`codmensagem`), KEY `dataenvio` (`dataenvio`,`horaenvio`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 Messages for table (mensagenspara) CREATE TABLE `mensagenspara` ( `codmensagempara` int(11) NOT NULL DEFAULT '0', `codmensagem` int(11) DEFAULT NULL, `codusuario` int(11) DEFAULT NULL, `situacao` int(11) DEFAULT NULL, `codvencimento` int(11) DEFAULT NULL, `datarecebimento` int(11) DEFAULT NULL, `horarecebimento` int(11) DEFAULT NULL, `dataleitura` int(11) DEFAULT NULL, `horaleitura` int(11) DEFAULT NULL, `codpasta` int(11) DEFAULT NULL, `codrespondida` int(11) DEFAULT NULL, `alarmedata` int(11) DEFAULT NULL, `alarmehora` int(11) DEFAULT NULL, `alarmetempo` int(11) DEFAULT NULL, `alarmetipo` int(11) DEFAULT NULL, `concluidodata` int(11) DEFAULT NULL, `concluidohora` int(11) DEFAULT NULL, `concluidomsg` varchar(255) DEFAULT NULL, `tipo` int(11) DEFAULT NULL, `avisado` int(11) DEFAULT NULL, `notificatarefanaocumprida` int(11) DEFAULT NULL, `notificatarefa` int(11) DEFAULT NULL, PRIMARY KEY (`codmensagempara`), KEY `codmensagempara` (`codmensagempara`,`codusuario`,`codpasta`,`codmensagem`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 The SQL used : SELECT m.codmensagem, m.codprioridade, m.codusuario, m.codmensagemoriginal, m.codmensagempai, m.assunto, m.dataenvio, m.horaenvio, m.datalimite, m.horalimite, m.anexo, m.tipo, u.nome, up.nome as nomepara, mp.codrespondida, mp.codmensagempara, mp.codusuario as codusuariopara, mp.situacao FROM cronosweb.mensagens m, cronosweb.mensagenspara mp, acessos.usuarios u, acessos.usuarios up WHERE m.ativo=1 AND m.codmensagem=mp.codmensagem AND m.codusuario=u.codusuario AND mp.codusuario=up.codusuario AND ( ( m.codpasta=1 AND m.codusuario = 916 AND m.situacao 0 ) OR ( mp.codpasta=1 AND mp.codusuario = 916 AND mp.situacao 4 ) ) GROUP BY m.codmensagem ORDER BY m.dataenvio DESC, m.horaenvio DESC, m.codmensagem DESC LIMIT 0,20 The time of this query takes to executes is about 13 ~ 15 seconds.
RE: quickly copying a database
InnoDB HotBackup but it costs money. http://www.innodb.com/hot-backup -Original Message- From: Ofer Inbar [mailto:[EMAIL PROTECTED] Sent: 18 July 2007 00:12 To: mysql@lists.mysql.com Subject: quickly copying a database I've got a server with a database that's about 10G. I need several other copies of this database, with different names, on the same host and same MySQL instance. I could mysqldump the db and then restore it into the others... mysql create database one; mysql create database two; ... mysqldump ... dumpfile.sql mysql -uroot -p one dumpfile.sql mysql -uroot -p two dumpfile.sql ... Unfortunately, each restore from a mysqldump takes about an hour (and if I do more than one at a time, they'd slow down considerable due to disk I/O contention). If these DBs were all MyISAM, I could shut down MySQL and just copy the directories. But it seems that InnoDB tables are stored partly in /var/lib/mysql/ibdata1 and this database has a mix of MyISAM and InnoDB. Is there a better technique to make several database copies quickly, that works for a mix of MyISAM and InnoDB? -- Cos -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] This email is confidential and may also be privileged. If you are not the intended recipient please notify us immediately by telephoning +44 (0)20 7452 5300 or email [EMAIL PROTECTED] You should not copy it or use it for any purpose nor disclose its contents to any other person. Touch Local cannot accept liability for statements made which are clearly the sender's own and are not made on behalf of the firm. Touch Local Limited Registered Number: 2885607 VAT Number: GB896112114 Cardinal Tower, 12 Farringdon Road, London EC1M 3NN +44 (0)20 7452 5300 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: BUG in UNION implementation?! Confimation or Explaination please
UNION is mean to removed duplicate rows. Use UNION ALL if you don't want this to happen. http://dev.mysql.com/doc/refman/5.0/en/union.html -Original Message- From: list account [mailto:[EMAIL PROTECTED] Sent: 11 July 2007 09:19 To: mysql@lists.mysql.com Subject: BUG in UNION implementation?! Confimation or Explaination please Hi all, I believe to have found a bug in MySQL's union implementation. Can someone confirm this, please or convince me that this is not a buggy behaviour of mysql : UNION seems to behave like DISTINCT by default: mysql select 2 c1 - union - select 1 c1 - union - select 2 c1 - union - select 1 c1; ++ | c1 | ++ | 2 | | 1 | ++ 2 rows in set (0.00 sec) mysql select 2 c1,1 union select 1 c1,2 union select 2 c1,3 union select 1 c1,4; ++---+ | c1 | 1 | ++---+ | 2 | 1 | | 1 | 2 | | 2 | 3 | | 1 | 4 | ++---+ 4 rows in set (0.00 sec) mysql select 2 c1,1 union select 1 c1,2 union select 2 c1,3 union select 1,2; ++---+ | c1 | 1 | ++---+ | 2 | 1 | | 1 | 2 | | 2 | 3 | ++---+ 3 rows in set (0.00 sec) mysql select avg(c1),avg(distinct c1),sum(c1),count(c1),count(distinct c1),count(*) from - ( - select 2 c1 - union - select 1 c1 - union - select 1 c1 - union - select 1 - ) a - ; +---++---+-+---+--+ |avg(c1)|avg(distinct c1)|sum(c1)|count(c1)|count(distinct c1) | count(*) | +---++---+-+---+--+ |1.5000 | 1.5000 | 3 | 2 | 2 |2 | +---++---+-+---+--+ 1 row in set (0.00 sec) but I would have expected: +---++---+-+---+--+ |avg(c1)|avg(distinct c1)|sum(c1)|count(c1)|count(distinct c1) | count(*) | +---++---+-+---+--+ |1.2500 | 1.5000 | 5 | 4 | 2 |4 | +---++---+-+---+--+ TIA, CVH This email is confidential and may also be privileged. If you are not the intended recipient please notify us immediately by telephoning +44 (0)20 7452 5300 or email [EMAIL PROTECTED] You should not copy it or use it for any purpose nor disclose its contents to any other person. Touch Local cannot accept liability for statements made which are clearly the sender's own and are not made on behalf of the firm. Touch Local Limited Registered Number: 2885607 VAT Number: GB896112114 Cardinal Tower, 12 Farringdon Road, London EC1M 3NN +44 (0)20 7452 5300 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: load data
Can you not change your proceedure and format your dates first using DAT_FORMAT()? http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function _date-format You could put a trigger on the table that would format the dates before insert (although I'd go for the above) -Original Message- From: Ananda Kumar [mailto:[EMAIL PROTECTED] Sent: 11 July 2007 06:30 To: MySQL General Subject: Fwd: load data Hi All, We have an application where we load data on a daily basis and then do some analysis and the move this data into different tables. Data is comming in files. The date format in the file is dd-mon- hh24:mi:ss', but as you all know, in mysql , the default date format is -mm-dd hh24:mi:ss. How can i specifiy this format in the load data infile script. I tried this LOAD DATA LOCAL INFILE 'abc.txt' INTO TABLE abc FIELDS TERMINATED BY ',' LINES TERMINATED BY '^V\n' (doj timestamp dd-mon- hh24:mi:ss); but data is not getting inserted. Please help me. regards anandkl This email is confidential and may also be privileged. If you are not the intended recipient please notify us immediately by telephoning +44 (0)20 7452 5300 or email [EMAIL PROTECTED] You should not copy it or use it for any purpose nor disclose its contents to any other person. Touch Local cannot accept liability for statements made which are clearly the sender's own and are not made on behalf of the firm. Touch Local Limited Registered Number: 2885607 VAT Number: GB896112114 Cardinal Tower, 12 Farringdon Road, London EC1M 3NN +44 (0)20 7452 5300 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Create Table Warning
Do a SHOW WARNINGS at the command line. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 13 June 2007 16:11 To: mysql@lists.mysql.com Subject: Create Table Warning This is a create table statement output from mysqldump from a 4.0.24 installation. Restoring on 5.0.22 gives a warning. Can anyone enlighten me? I guess I'm a little behind on my reading... David mysql CREATE TABLE `container` ( - `carrier` varchar(128) NOT NULL default '', - `lock1` varchar(7) NOT NULL default '', - `lock2` varchar(7) NOT NULL default '', - `lock3` varchar(7) NOT NULL default '', - `lock4` varchar(7) NOT NULL default '', - `lock5` varchar(7) NOT NULL default '', - `lock6` varchar(7) NOT NULL default '', - `lock7` varchar(7) NOT NULL default '', - `lock8` varchar(7) NOT NULL default '', - `lock9` varchar(7) NOT NULL default '', - `lock10` varchar(7) NOT NULL default '', - `lock11` varchar(7) NOT NULL default '', - `lock12` varchar(7) NOT NULL default '', - `lock13` varchar(7) NOT NULL default '', - `lock14` varchar(7) NOT NULL default '', - `lock15` varchar(7) NOT NULL default '', - `lock16` varchar(7) NOT NULL default '', - `lock17` varchar(7) NOT NULL default '', - `lock18` varchar(7) NOT NULL default '', - `lock19` varchar(7) NOT NULL default '', - `lock20` varchar(7) NOT NULL default '', - `lock21` varchar(7) NOT NULL default '', - `lock22` varchar(7) NOT NULL default '', - `lock23` varchar(7) NOT NULL default '', - `lock24` varchar(7) NOT NULL default '', - PRIMARY KEY (`carrier`) - ) TYPE=MyISAM; Query OK, 0 rows affected, 1 warning (0.00 sec) This email is confidential and may also be privileged. If you are not the intended recipient please notify us immediately by telephoning +44 (0)20 7452 5300 or email [EMAIL PROTECTED] You should not copy it or use it for any purpose nor disclose its contents to any other person. Touch Local cannot accept liability for statements made which are clearly the sender's own and are not made on behalf of the firm. Touch Local Limited Registered Number: 2885607 VAT Number: GB896112114 Cardinal Tower, 12 Farringdon Road, London EC1M 3NN +44 (0)20 7452 5300 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Find and Add Unmatched Records
The REPLACE staement might work for you... http://dev.mysql.com/doc/refman/4.1/en/replace.html... -Original Message- From: Kebbel, John [mailto:[EMAIL PROTECTED] Sent: 01 June 2007 15:31 To: mysql@lists.mysql.com Subject: Find and Add Unmatched Records I have a table of middle school students I want to update quarterly. I am only interested in finding and adding new students (not in finding and deleting students who have been dropped from the school district). I'm pretty sure I could put together a PHP script that would do this, but is there a MySQL way for one MySQL table (preceding quarter) to scan another MySQL table (current quarter) by primary key (student id number) and absorb the unmatched, new students? I'm thinking that if f I could bypass PHP, I could use MySQL triggers to automatically add new records to other, related tables. This email is confidential and may also be privileged. If you are not the intended recipient please notify us immediately by telephoning +44 (0)20 7452 5300 or email [EMAIL PROTECTED] You should not copy it or use it for any purpose nor disclose its contents to any other person. Touch Local cannot accept liability for statements made which are clearly the sender's own and are not made on behalf of the firm. Touch Local Limited Registered Number: 2885607 VAT Number: GB896112114 Cardinal Tower, 12 Farringdon Road, London EC1M 3NN +44 (0)20 7452 5300 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Integrity on large sites
In my experience this happens a lot if you put application programmers in charge of the database. I've upset quite a few in my time by introducing RI and then their horribly coded application falls over! -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: 24 May 2007 17:31 To: Naz Gassiep Cc: mysql@lists.mysql.com Subject: Re: Integrity on large sites Naz, *Really* big sites don't ever have referential integrity. Or if the few spots they do (like with financial transactions) it's implemented on the application level (via, say, optimistic locking), never the database level. Mebbe that view was common in the MySQL community in the time of version 3, when the emphasis was on one site managing one db. Agreed the concept is scary. Try that quote in an Oracle or MSSQL community :-) PB - Naz Gassiep wrote: I'm working in a project at the moment that is using MySQL, and people keep making assertions like this one: *Really* big sites don't ever have referential integrity. Or if the few spots they do (like with financial transactions) it's implemented on the application level (via, say, optimistic locking), never the database level. A large DB working with no RI would give me nightmares. Is it really true that large sites turn RI off to improve performance? Am I just being naive in thinking that everyone runs their DBs with RI in production? This email is confidential and may also be privileged. If you are not the intended recipient please notify us immediately by telephoning +44 (0)20 7452 5300 or email [EMAIL PROTECTED] You should not copy it or use it for any purpose nor disclose its contents to any other person. Touch Local cannot accept liability for statements made which are clearly the sender's own and are not made on behalf of the firm. Touch Local Limited Registered Number: 2885607 VAT Number: GB896112114 Cardinal Tower, 12 Farringdon Road, London EC1M 3NN +44 (0)20 7452 5300 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
