update, insert ... gee.. a puzzle

[lejeczek]

..  that you experts I hope can crack like a digestive 
biscuit...


how does one update, or merge or whatever is right technical 
term for it - a my.table from my.another table (both are 
schematically identical, no foreign keys, one primary key) but..
does it a way so when there is a duplicate only NULLs in 
my.table get updated/replaced with proper values from 
my.another table?


many thanks, specially for actual syntax hints.
L.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: update, insert ... gee.. a puzzle

[Hal.sz S.ndor]

2016/03/17 12:47 ... lejeczek:

..  that you experts I hope can crack like a digestive biscuit...

how does one update, or merge or whatever is right technical term for it
- a my.table from my.another table (both are schematically identical, no
foreign keys, one primary key) but..
does it a way so when there is a duplicate only NULLs in my.table get
updated/replaced with proper values from my.another table?

many thanks, specially for actual syntax hints.

Nothing here is easy

My best is INSERT ... SELECT ... ON DUPLICATE KEY UPDATE ...;

INSERT INTO my.table SELECT * FROM my."another table" ON DUPLICATE KEY 
UPDATE /* every single field "f" not in the key thus: */ f = 
IFNULL(my.table.f, my."another table".f);


By the way, there always is only one primary key. Maybe you mean that 
there is only one field to it?


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: How to get auto Increment ID of INSERT?

[Carsten Pedersen]

LAST_INSERT_ID() returns the latest ID for the current connection. As 
long as you yourself can guarantee that no other queries are executed 
using that connection, you're fine. If another record others is inserted 
using another connection, that connection will return a different 
LAST_INSERT_ID().


Best,

/ Carsten

On 08-10-2015 15:48, Richard Reina wrote:

If I insert a record into a table with an auto increment ID how can I get
that records ID value? I have read about SELECT LAST_INSERT_ID() statement,
however, do not two statements introduce the risk that another insert may
occur in the interum? Is there a full proof way of getting the ID of the
record that you have just inserted?

Thanks



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

How to get auto Increment ID of INSERT?

[Richard Reina]

If I insert a record into a table with an auto increment ID how can I get
that records ID value? I have read about SELECT LAST_INSERT_ID() statement,
however, do not two statements introduce the risk that another insert may
occur in the interum? Is there a full proof way of getting the ID of the
record that you have just inserted?

Thanks

Re: MySQL 5.5 Slow performance to insert

[shawn l.green]

On 7/24/2015 4:35 PM, Camilo Vieira wrote:

Hi,

My MySQL server is performing very slow inserts. Does somebody could help
me to understand what's happening?
... snip ...
---TRANSACTION 31D6D74, ACTIVE 27107 sec
mysql tables in use 8, locked 8
7470 lock struct(s), heap size 801208, 849088 row lock(s)
MySQL thread id 15361, OS thread handle 0x7fea5e5c2700, query id 2690080
10.180.17.252 root Copying to tmp table
insert into CONFERENCIA_ENCALHE
(data, preco_capa_informado, qtde, qtde_informada,
chamada_encalhe_cota_id, controle_conferencia_encalhe_cota_id,
movimento_estoque_id, movimento_estoque_cota_id,
produto_edicao_id,juramentada)
(select distinct data_recolhimento, pe.preco_previsto, mec.qtde, mec.qtde,
cec.id, ccec.id, me.id, mec.id, mec.produto_edicao_id,0
from
movimento_estoque_cota_memoria mec,
movimento_estoque_memoria me,
chamada_encalhe ce,
chamada_encalhe_cota cec,
controle_conferencia_encalhe cce,
controle_conferencia_encalhe_cota ccec,
produto_edicao pe
where
mec.tipo_movimento_id =

...snip...


--
BUFFER POOL AND MEMORY
--
Total memory allocated 2197815296; in additional pool allocated 0
Dictionary memory allocated 28294038
Buffer pool size   131071
Free buffers   0
Database pages 123957
Old database pages 45737



Here are the two things I noticed:

1) You are using a subquery in this INSERT command instead of a naked 
SELECT. How long does it take that query to execute in isolation 
(outside of an INSERT command) ?


The correct syntax would be to skip the parentheses around the SELECT 
portion of the command. Instead of this,


insert into CONFERENCIA_ENCALHE(...) (select distinct data_recolhimento, 
pe.preco_previsto, mec.qtde, ...


, do this,

insert into CONFERENCIA_ENCALHE(...) SELECT distinct data_recolhimento, 
pe.preco_previsto, mec.qtde, ...



2) You have allocated very little memory to your InnoDB Buffer Pool. 
131071 pages = 2GB.  Depending on how much data you are attempting to 
first SELECT from seven tables then INSERT into the other, you may be 
forcing the system to do a lot of disk-level intermediate storage which 
is much slower than buffering that same information in memory. However, 
that may be all the RAM you can spare for MySQL. If that is so, then 
there is little you can do about this particular part of the problem.


Yours,
--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services
Office: Blountville, TN

Become certified in MySQL! Visit https://www.mysql.com/certification/ 
for details.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: MySQL 5.5 Slow performance to insert

[Johan De Meersman]

- Original Message -
> From: "Camilo Vieira" 
> Subject: Re: MySQL 5.5 Slow performance to insert

> $ ./mysqltuner.pl --user root --pass abril@123

Thank you for that password :-) I don't particularly like MySQLtuner myself, it 
makes assumptions about your workload that are, imo, false more often than not. 
Anyway...

> [!!] Total fragmented tables: 284

*shrug* Small issue on modern storage, pretty much no issue on solid state.

> [!!] Key buffer used: 18.2% (3M used / 16M cache)

*shrug* You could shrink that if you have no MyISAM at all, but it's a 
neglectable amount of memory.

> [!!] Query cache is disabled

Which is pretty much irrelevant for an insert workload :-)

> [!!] Table cache hit rate: 1% (400 open / 30K opened)

That might be worth growing. Exact numbers are hard to point at, use munin or 
similar to figure out the growth rate of opened_tables.

> [!!] InnoDB  buffer pool / data size: 4.0G/52.4G

If possible, size your buffer pool to at least your active dataset. This will 
have a considerable impact on your initial insert speed.

> [!!] InnoDB buffer pool instances: 1

>From 5.6 (I think) this defaults to 8. The benefit is less contention for 
>mutexes and the like. Upgrading to 5.6 is worthwile in most cases anyway.

> [!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)

Hmm. That's peculiar for an insert workload... did you run that after (or 
during) the workload?


Your original mail also doesn't seem to specify the workload beyond 'inserts'. 
Details?

-- 
Unhappiness is discouraged and will be corrected with kitten pictures.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: MySQL 5.5 Slow performance to insert

[Camilo Vieira]

nds per wait: 8.75 mutex, 13.93 RW-shared, 13.72 RW-excl
>> 
>> LATEST FOREIGN KEY ERROR
>> 
>> 150722 18:23:08 Transaction:
>> TRANSACTION 31AC5E3, ACTIVE 8 sec inserting
>> mysql tables in use 3, locked 3
>> 826 lock struct(s), heap size 96696, 31241 row lock(s), undo log entries
>> 9932
>> MySQL thread id 932, OS thread handle 0x7feaf0042700, query id 42396
>> 10.180.17.252 root
>> insert into movimento_estoque_cota (
>>
>> ID,APROVADO_AUTOMATICAMENTE,DATA_APROVACAO,STATUS,DATA,DATA_CRIACAO,TIPO_MOVIMENTO_ID,USUARIO_ID,
>>
>> QTDE,PRODUTO_EDICAO_ID,COTA_ID,ESTOQUE_PROD_COTA_ID,ORIGEM,APROVADOR_ID,LANCAMENTO_ID,status_estoque_financeiro,
>> PRECO_COM_DESCONTO,PRECO_VENDA,VALOR_DESCONTO,FORMA_COMERCIALIZACAO)
>> (select
>>
>> ID,true,date(sysdate()),'APROVADO',DATA,date(sysdate()),TIPO_MOVIMENTO_ID,1,
>> QTDE,PRODUTO_EDICAO_ID,COTA_ID,
>> ESTOQUE_PROD_COTA_ID,
>> 'CARGA_INICIAL',1,LANCAMENTO_ID,
>>
>> IF(STATUS_ESTOQUE_FINANCEIRO=1,'FINANCEIRO_PROCESSADO','FINANCEIRO_NAO_PROCESSADO'),
>> PRECO_COM_DESCONTO,PRECO_
>> Foreign key constraint fails for table
>> `teste_historico`.`movimento_estoque_cota`:
>> ,
>>   CONSTRAINT `FK459444C362506D6B` FOREIGN KEY (`ESTOQUE_PROD_COTA_ID`)
>> REFERENCES `estoque_produto_cota` (`ID`)
>> Trying to add in child table, in index `FK459444C362506D6B` tuple:
>> DATA TUPLE: 2 fields;
>>  0: len 8; hex 80054211; asc   B ;;
>>  1: len 8; hex 8009743f; asc   t?;;
>>
>> But in parent table `teste_historico`.`estoque_produto_cota`, in index
>> `PRIMARY`,
>> the closest match we can find is record:
>> PHYSICAL RECORD: n_fields 7; compact format; info bits 0
>>  0: len 8; hex 80053f0d; asc   ? ;;
>>  1: len 6; hex 031aba25; asc  %;;
>>  2: len 7; hex 85561a1a14; ascV   ;;
>>  3: len 9; hex 80; asc  ;;
>>  4: len 9; hex 80003c; asc   <  ;;
>>  5: len 8; hex 8044; ascD;;
>>  6: len 8; hex 8001007d; asc};;
>>
>> 
>> TRANSACTIONS
>> 
>> Trx id counter 31DDD14
>> Purge done for trx's n:o < 31D8A49 undo n:o < 0
>> History list length 425
>> LIST OF TRANSACTIONS FOR EACH SESSION:
>> ---TRANSACTION 0, not started
>> MySQL thread id 18036, OS thread handle 0x7fea5e591700, query id 2854409
>> localhost root
>> show engine innodb status
>> ---TRANSACTION 31DDD13, not started
>> MySQL thread id 16024, OS thread handle 0x7fea5e4cd700, query id 2854407
>> 10.129.28.111 root
>> ---TRANSACTION 31DC01D, not started
>> MySQL thread id 15932, OS thread handle 0x7fea5e52f700, query id 2812134
>> 10.129.28.111 root
>> ---TRANSACTION 31D83B4, not started
>> MySQL thread id 15931, OS thread handle 0x7fea5d714700, query id 2721807
>> 10.129.28.111 root
>> ---TRANSACTION 31D886C, not started
>> MySQL thread id 15930, OS thread handle 0x7fea5d776700, query id 2730189
>> 10.129.28.111 root
>> ---TRANSACTION 31D8396, not started
>> MySQL thread id 15924, OS thread handle 0x7feaf0073700, query id 2721613
>> 10.129.28.111 root
>> ---TRANSACTION 31D6D74, ACTIVE 27107 sec
>> mysql tables in use 8, locked 8
>> 7470 lock struct(s), heap size 801208, 849088 row lock(s)
>> MySQL thread id 15361, OS thread handle 0x7fea5e5c2700, query id 2690080
>> 10.180.17.252 root Copying to tmp table
>> insert into CONFERENCIA_ENCALHE
>> (data, preco_capa_informado, qtde, qtde_informada,
>> chamada_encalhe_cota_id, controle_conferencia_encalhe_cota_id,
>> movimento_estoque_id, movimento_estoque_cota_id,
>> produto_edicao_id,juramentada)
>> (select distinct data_recolhimento, pe.preco_previsto, mec.qtde, mec.qtde,
>> cec.id, ccec.id, me.id, mec.id, mec.produto_edicao_id,0
>> from
>> movimento_estoque_cota_memoria mec,
>> movimento_estoque_memoria me,
>> chamada_encalhe ce,
>> chamada_encalhe_cota cec,
>> controle_conferencia_encalhe cce,
>> controle_conferencia_encalhe_cota ccec,
>> produto_edicao pe
>> where
>> mec.tipo_movimento_id =
>> 
>> FILE I/O
>> 
>> I/O thread 0 state: waiting for i/o request (insert buffer thread)
>> I/O thread 1 state: waiting for i/o request (log thread)
>> I/O thread 2 state: waiting for i/o request (read thread)
>> I/O thread 3 state: waiting for i/o request (read thread)
>> I/O thread 4 state: waiting for i/o request (read thread)

Re: MySQL 5.5 Slow performance to insert

[yoku ts.]

Hi,

Your INSEERquery status is "Copying to tmp table",
this means fetching rows which has to be inserted is slow.

You should tune SELECT statement in your insert query.
Adding indexes and/or simplifying query and/or .. so on.


```
---TRANSACTION 31D6D74, ACTIVE 27107 sec
mysql tables in use 8, locked 8
7470 lock struct(s), heap size 801208, 849088 row lock(s)
MySQL thread id 15361, OS thread handle 0x7fea5e5c2700, query id 2690080
10.180.17.252 root Copying to tmp table
insert into CONFERENCIA_ENCALHE
(data, preco_capa_informado, qtde, qtde_informada,
chamada_encalhe_cota_id, controle_conferencia_encalhe_cota_id,
movimento_estoque_id, movimento_estoque_cota_id,
produto_edicao_id,juramentada)
(select distinct data_recolhimento, pe.preco_previsto, mec.qtde, mec.qtde,
cec.id, ccec.id, me.id, mec.id, mec.produto_edicao_id,0
from
movimento_estoque_cota_memoria mec,
movimento_estoque_memoria me,
chamada_encalhe ce,
chamada_encalhe_cota cec,
controle_conferencia_encalhe cce,
controle_conferencia_encalhe_cota ccec,
produto_edicao pe
where
mec.tipo_movimento_id =
```


yoku0825,

2015-07-25 5:35 GMT+09:00 Camilo Vieira :
> Hi,
>
> My MySQL server is performing very slow inserts. Does somebody could help
> me to understand what's happening?
>
> mysql> show engine innodb status \G
> *** 1. row ***
>   Type: InnoDB
>   Name:
> Status:
> =
> 150724 17:40:28 INNODB MONITOR OUTPUT
> =
> Per second averages calculated from the last 1 seconds
> -
> BACKGROUND THREAD
> -
> srv_master_thread loops: 173967 1_second, 173966 sleeps, 17386 10_second,
> 191 background, 191 flush
> srv_master_thread log flush and writes: 174022
> --
> SEMAPHORES
> --
> OS WAIT ARRAY INFO: reservation count 40941, signal count 55139
> Mutex spin waits 101247, rounds 886301, OS waits 24221
> RW-shared spins 29684, rounds 413636, OS waits 11014
> RW-excl spins 18765, rounds 257448, OS waits 5190
> Spin rounds per wait: 8.75 mutex, 13.93 RW-shared, 13.72 RW-excl
> 
> LATEST FOREIGN KEY ERROR
> 
> 150722 18:23:08 Transaction:
> TRANSACTION 31AC5E3, ACTIVE 8 sec inserting
> mysql tables in use 3, locked 3
> 826 lock struct(s), heap size 96696, 31241 row lock(s), undo log entries
> 9932
> MySQL thread id 932, OS thread handle 0x7feaf0042700, query id 42396
> 10.180.17.252 root
> insert into movimento_estoque_cota (
> ID,APROVADO_AUTOMATICAMENTE,DATA_APROVACAO,STATUS,DATA,DATA_CRIACAO,TIPO_MOVIMENTO_ID,USUARIO_ID,
> QTDE,PRODUTO_EDICAO_ID,COTA_ID,ESTOQUE_PROD_COTA_ID,ORIGEM,APROVADOR_ID,LANCAMENTO_ID,status_estoque_financeiro,
> PRECO_COM_DESCONTO,PRECO_VENDA,VALOR_DESCONTO,FORMA_COMERCIALIZACAO)
> (select
> ID,true,date(sysdate()),'APROVADO',DATA,date(sysdate()),TIPO_MOVIMENTO_ID,1,
> QTDE,PRODUTO_EDICAO_ID,COTA_ID,
> ESTOQUE_PROD_COTA_ID,
> 'CARGA_INICIAL',1,LANCAMENTO_ID,
> IF(STATUS_ESTOQUE_FINANCEIRO=1,'FINANCEIRO_PROCESSADO','FINANCEIRO_NAO_PROCESSADO'),
> PRECO_COM_DESCONTO,PRECO_
> Foreign key constraint fails for table
> `teste_historico`.`movimento_estoque_cota`:
> ,
>   CONSTRAINT `FK459444C362506D6B` FOREIGN KEY (`ESTOQUE_PROD_COTA_ID`)
> REFERENCES `estoque_produto_cota` (`ID`)
> Trying to add in child table, in index `FK459444C362506D6B` tuple:
> DATA TUPLE: 2 fields;
>  0: len 8; hex 80054211; asc   B ;;
>  1: len 8; hex 8009743f; asc   t?;;
>
> But in parent table `teste_historico`.`estoque_produto_cota`, in index
> `PRIMARY`,
> the closest match we can find is record:
> PHYSICAL RECORD: n_fields 7; compact format; info bits 0
>  0: len 8; hex 80053f0d; asc   ? ;;
>  1: len 6; hex 031aba25; asc  %;;
>  2: len 7; hex 85561a1a14; ascV   ;;
>  3: len 9; hex 80; asc  ;;
>  4: len 9; hex 80003c; asc   <  ;;
>  5: len 8; hex 8044; ascD;;
>  6: len 8; hex 8001007d; asc};;
>
> 
> TRANSACTIONS
> 
> Trx id counter 31DDD14
> Purge done for trx's n:o < 31D8A49 undo n:o < 0
> History list length 425
> LIST OF TRANSACTIONS FOR EACH SESSION:
> ---TRANSACTION 0, not started
> MySQL thread id 18036, OS thread handle 0x7fea5e591700, query id 2854409
> localhost root
> show engine innodb status
> ---TRANSACTION 31DDD13, not started
> MySQL thread id 16024, OS thread handle 0x7fea5e4cd700, query id 2854407
> 10.129.28.111 root
> ---TRANSACTION 31DC01D, not started
> MySQL thread id 15932, OS thread handle 0x7fea5e52f700, query id 2812134
> 10.129.28.111 root
>

MySQL 5.5 Slow performance to insert

[Camilo Vieira]

Hi,

My MySQL server is performing very slow inserts. Does somebody could help
me to understand what's happening?

mysql> show engine innodb status \G
*** 1. row ***
  Type: InnoDB
  Name:
Status:
=
150724 17:40:28 INNODB MONITOR OUTPUT
=
Per second averages calculated from the last 1 seconds
-
BACKGROUND THREAD
-
srv_master_thread loops: 173967 1_second, 173966 sleeps, 17386 10_second,
191 background, 191 flush
srv_master_thread log flush and writes: 174022
--
SEMAPHORES
--
OS WAIT ARRAY INFO: reservation count 40941, signal count 55139
Mutex spin waits 101247, rounds 886301, OS waits 24221
RW-shared spins 29684, rounds 413636, OS waits 11014
RW-excl spins 18765, rounds 257448, OS waits 5190
Spin rounds per wait: 8.75 mutex, 13.93 RW-shared, 13.72 RW-excl

LATEST FOREIGN KEY ERROR

150722 18:23:08 Transaction:
TRANSACTION 31AC5E3, ACTIVE 8 sec inserting
mysql tables in use 3, locked 3
826 lock struct(s), heap size 96696, 31241 row lock(s), undo log entries
9932
MySQL thread id 932, OS thread handle 0x7feaf0042700, query id 42396
10.180.17.252 root
insert into movimento_estoque_cota (
ID,APROVADO_AUTOMATICAMENTE,DATA_APROVACAO,STATUS,DATA,DATA_CRIACAO,TIPO_MOVIMENTO_ID,USUARIO_ID,
QTDE,PRODUTO_EDICAO_ID,COTA_ID,ESTOQUE_PROD_COTA_ID,ORIGEM,APROVADOR_ID,LANCAMENTO_ID,status_estoque_financeiro,
PRECO_COM_DESCONTO,PRECO_VENDA,VALOR_DESCONTO,FORMA_COMERCIALIZACAO)
(select
ID,true,date(sysdate()),'APROVADO',DATA,date(sysdate()),TIPO_MOVIMENTO_ID,1,
QTDE,PRODUTO_EDICAO_ID,COTA_ID,
ESTOQUE_PROD_COTA_ID,
'CARGA_INICIAL',1,LANCAMENTO_ID,
IF(STATUS_ESTOQUE_FINANCEIRO=1,'FINANCEIRO_PROCESSADO','FINANCEIRO_NAO_PROCESSADO'),
PRECO_COM_DESCONTO,PRECO_
Foreign key constraint fails for table
`teste_historico`.`movimento_estoque_cota`:
,
  CONSTRAINT `FK459444C362506D6B` FOREIGN KEY (`ESTOQUE_PROD_COTA_ID`)
REFERENCES `estoque_produto_cota` (`ID`)
Trying to add in child table, in index `FK459444C362506D6B` tuple:
DATA TUPLE: 2 fields;
 0: len 8; hex 80054211; asc   B ;;
 1: len 8; hex 8009743f; asc   t?;;

But in parent table `teste_historico`.`estoque_produto_cota`, in index
`PRIMARY`,
the closest match we can find is record:
PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 8; hex 80053f0d; asc   ? ;;
 1: len 6; hex 031aba25; asc  %;;
 2: len 7; hex 85561a1a14; ascV   ;;
 3: len 9; hex 80; asc  ;;
 4: len 9; hex 80003c; asc   <  ;;
 5: len 8; hex 8044; ascD;;
 6: len 8; hex 8001007d; asc};;


TRANSACTIONS

Trx id counter 31DDD14
Purge done for trx's n:o < 31D8A49 undo n:o < 0
History list length 425
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 18036, OS thread handle 0x7fea5e591700, query id 2854409
localhost root
show engine innodb status
---TRANSACTION 31DDD13, not started
MySQL thread id 16024, OS thread handle 0x7fea5e4cd700, query id 2854407
10.129.28.111 root
---TRANSACTION 31DC01D, not started
MySQL thread id 15932, OS thread handle 0x7fea5e52f700, query id 2812134
10.129.28.111 root
---TRANSACTION 31D83B4, not started
MySQL thread id 15931, OS thread handle 0x7fea5d714700, query id 2721807
10.129.28.111 root
---TRANSACTION 31D886C, not started
MySQL thread id 15930, OS thread handle 0x7fea5d776700, query id 2730189
10.129.28.111 root
---TRANSACTION 31D8396, not started
MySQL thread id 15924, OS thread handle 0x7feaf0073700, query id 2721613
10.129.28.111 root
---TRANSACTION 31D6D74, ACTIVE 27107 sec
mysql tables in use 8, locked 8
7470 lock struct(s), heap size 801208, 849088 row lock(s)
MySQL thread id 15361, OS thread handle 0x7fea5e5c2700, query id 2690080
10.180.17.252 root Copying to tmp table
insert into CONFERENCIA_ENCALHE
(data, preco_capa_informado, qtde, qtde_informada,
chamada_encalhe_cota_id, controle_conferencia_encalhe_cota_id,
movimento_estoque_id, movimento_estoque_cota_id,
produto_edicao_id,juramentada)
(select distinct data_recolhimento, pe.preco_previsto, mec.qtde, mec.qtde,
cec.id, ccec.id, me.id, mec.id, mec.produto_edicao_id,0
from
movimento_estoque_cota_memoria mec,
movimento_estoque_memoria me,
chamada_encalhe ce,
chamada_encalhe_cota cec,
controle_conferencia_encalhe cce,
controle_conferencia_encalhe_cota ccec,
produto_edicao pe
where
mec.tipo_movimento_id =

FILE I/O

I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for

Problem with INSERT INTO and UPDATE queries

[Antonio Fernández Pérez]

​Hi list,

I have some problems with INSERT INTO and UPDATE queries on a big table.
Let me put the code and explain it ...

I have copied the create code of the table. This table has more than
1500 rows.

​Create Table: CREATE TABLE `radacct` (
  `RadAcctId` bigint(21) NOT NULL AUTO_INCREMENT,
  `AcctSessionId` varchar(32) NOT NULL DEFAULT '',
  `AcctUniqueId` varchar(32) NOT NULL DEFAULT '',
  `UserName` varchar(64) NOT NULL DEFAULT '',
  `Realm` varchar(64) DEFAULT '',
  `NASIPAddress` varchar(15) NOT NULL DEFAULT '',
  `NASPortId` varchar(15) DEFAULT NULL,
  `NASPortType` varchar(32) DEFAULT NULL,
  `AcctStartTime` datetime NOT NULL DEFAULT '-00-00 00:00:00',
  `AcctStopTime` datetime NOT NULL DEFAULT '-00-00 00:00:00',
  `AcctSessionTime` int(12) DEFAULT NULL,
  `AcctAuthentic` varchar(32) DEFAULT NULL,
  `ConnectInfo_start` varchar(50) DEFAULT NULL,
  `ConnectInfo_stop` varchar(50) DEFAULT NULL,
  `AcctInputOctets` bigint(20) DEFAULT NULL,
  `AcctOutputOctets` bigint(20) DEFAULT NULL,
  `CalledStationId` varchar(50) NOT NULL DEFAULT '',
  `CallingStationId` varchar(50) NOT NULL DEFAULT '',
  `AcctTerminateCause` varchar(32) NOT NULL DEFAULT '',
  `ServiceType` varchar(32) DEFAULT NULL,
  `FramedProtocol` varchar(32) DEFAULT NULL,
  `FramedIPAddress` varchar(15) NOT NULL DEFAULT '',
  `AcctStartDelay` int(12) DEFAULT NULL,
  `AcctStopDelay` int(12) DEFAULT NULL,
  `XAscendSessionSvrKey` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`RadAcctId`),
  KEY `user_start` (`UserName`,`AcctStartTime`),
  KEY `nasip_starttime` (`NASIPAddress`,`AcctStartTime`),
  KEY `stop_nasip_start` (`AcctStopTime`,`NASIPAddress`,`AcctStartTime`),
  KEY `acctsesid_user_nasip` (`UserName`,`AcctSessionId`,`NASIPAddress`),
  KEY `user_stop` (`UserName`,`AcctStopTime`)
) ENGINE=InnoDB AUTO_INCREMENT=17694651 DEFAULT CHARSET=utf8

###

The next text shows the entries in mysql-slow.log.

###

# Time: 140625  9:37:45
# User@Host: radius[radius] @  [192.168.0.30]
# Thread_id: 94892163  Schema: radius  Last_errno: 0  Killed: 0
# Query_time: 2.327159  Lock_time: 0.86  Rows_sent: 0  Rows_examined:
0  Rows_affected: 1  Rows_read: 0
# Bytes_sent: 19
use radius;
SET timestamp=1403681865;
INSERT INTO radacct (acctsessionid,acctuniqueid,
username,  realm,nasipaddress,
nasportid,  nasporttype,  acctstarttime,
acctstoptime,  acctsessiontime,  acctau
thentic,connectinfo_start,  connectinfo_stop,
acctinputoctets,  acctoutputoctets,  calledstationid,
callingstationid, acctterminatecause,  servicetype,
framedprotocol,   framedipaddress,
   acctstartdelay,   acctstopdelay,xascendsessionsvrkey)
VALUES ('80004ef0', '78d3fc2661258da5',
'zu629LAYUT',  '', '178.136.71.251', '2147503856',
'Wireless-802.11', '2014
-06-25 09:37:26', '-00-00 00:00:00',  '0', '',
'',  '', '0', '0',  'tururu', '00-00-11-11-11-11',
'',  '', '', '178.136.71.1',  '0', '0', '');
# User@Host: radius[radius] @  [192.168.0.31]
# Thread_id: 97905294  Schema: radius  Last_errno: 0  Killed: 0
# Query_time: 2.397604  Lock_time: 0.62  Rows_sent: 0  Rows_examined:
1  Rows_affected: 1  Rows_read: 1
# Bytes_sent: 52
SET timestamp=1403681865;
UPDATE radacct   SET  framedipaddress =
'182.138.214.240',  acctsessiontime = '4199',
acctinputoctets = '0'  << 32 |
'12327909',  acctoutputo
ctets= '0' << 32 |
'294177486'   WHERE acctsessionid = '805063b1'   AND
username= 'fa239DADUX'   AND nasipaddress=
'182.138.214.50';

###

The previous query is converted because I want to use EXPLAIN ...

###

SELECT framedipaddress = '172.21.13.152',  acctsessiontime
= '4199',  acctinputoctets = '0'  << 32
|'12327909',  acctoutputo
ctets= '0' << 32 |
'294177486'
FROM radacct
WHERE acctsessionid = '805063b1'   AND username=
'fa239DADUX'   AND nasipaddress= 

Re: Really slow batch insert??

[hsv]

>>>> 2013/12/30 13:59 +0200, Cabbar Duzayak >>>>
We have a basic table, which is something like :


(id varchar50, productId varchar50, category varchar50)


In this table, ID is the primary key and we have a unique index on
(category, productId). And, there is a case where we want to do bulk
inserts (3000 inserts) using:


INSERT INTO (id, productId, category) VALUES (‘x1’, ‘y1’, ‘z1’), (‘x2’,
‘y2’, ‘z3’) ….. ON DUPLICATE KEY productId = VALUES(productId), category =
VALUES(category)


So, when we try to insert 3000 rows using this syntax with a single
statement, it takes ~ 3 seconds to execute this on an empty table.


BTW, innodb_flush_log_at_trx_commit is set to  2 for us.


I don’t have something factual data here, but it feels that this is too
much just for 3000 rows.
<<<<<<<<
I have nothing real to say about the slowness, but it looks as if in this case 
your operation is the same as

REPLACE INTO (id, productId, category) VALUES (‘x1’, ‘y1’, ‘z1’), (‘x2’,
‘y2’, ‘z3’)

and maybe that takes less time.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Really slow batch insert??

[Cabbar Duzayak]

Ah, forgot to mention that table is INNODB.


On Mon, Dec 30, 2013 at 1:59 PM, Cabbar Duzayak  wrote:

> Hi,
>
>
> We have a basic table, which is something like :
>
>
> (id varchar50, productId varchar50, category varchar50)
>
>
> In this table, ID is the primary key and we have a unique index on
> (category, productId). And, there is a case where we want to do bulk
> inserts (3000 inserts) using:
>
>
> INSERT INTO (id, productId, category) VALUES (‘x1’, ‘y1’, ‘z1’), (‘x2’,
> ‘y2’, ‘z3’) ….. ON DUPLICATE KEY productId = VALUES(productId), category =
> VALUES(category)
>
>
> So, when we try to insert 3000 rows using this syntax with a single
> statement, it takes ~ 3 seconds to execute this on an empty table.
>
>
> BTW, innodb_flush_log_at_trx_commit is set to  2 for us.
>
>
> I don’t have something factual data here, but it feels that this is too
> much just for 3000 rows.
>
>
> Isn’t this just too slow? Are we doing something wrong here? Or, can you
> advice as to which configuration parameters can we tweak here to make it
> faster?
>
>
> Thanks
>

Really slow batch insert??

[Cabbar Duzayak]

Hi,


We have a basic table, which is something like :


(id varchar50, productId varchar50, category varchar50)


In this table, ID is the primary key and we have a unique index on
(category, productId). And, there is a case where we want to do bulk
inserts (3000 inserts) using:


INSERT INTO (id, productId, category) VALUES (‘x1’, ‘y1’, ‘z1’), (‘x2’,
‘y2’, ‘z3’) ….. ON DUPLICATE KEY productId = VALUES(productId), category =
VALUES(category)


So, when we try to insert 3000 rows using this syntax with a single
statement, it takes ~ 3 seconds to execute this on an empty table.


BTW, innodb_flush_log_at_trx_commit is set to  2 for us.


I don’t have something factual data here, but it feels that this is too
much just for 3000 rows.


Isn’t this just too slow? Are we doing something wrong here? Or, can you
advice as to which configuration parameters can we tweak here to make it
faster?


Thanks

Re: MUltiple value in single insert is not working in mysql procedure

[hsv]

 2012/12/03 19:10 +0530, amit 
Problem
mysql> call mobile_series1('(99889988),(12334565)');

You are expecting MySQL to turn one string operand into twain number operands. 
That does not happen, unless you use PREPARE, which, I suspect, is not part of 
your homework. 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: MUltiple value in single insert is not working in mysql procedure

[Peter Brawley]

On 2012-12-03 7:40 AM, amit wrote:

Hi Team,

I am not able to use multi value insert via argument in mysql stored 
procedure, Please help where am I wrong. Thanks in Advance !


mysql> insert into input_data1 values(),(),(),();
Query OK, 4 rows affected (0.00 sec)

*Problem*
mysql> call mobile_series1('(99889988),(12334565)');
+---+
| mobile|
+---+
| (99889988),(12334565) |
+---+
1 row in set (0.01 sec)

instead of 2 rows in table its only one row.

*Function Code:*
|DELIMITER|
DROP  PROCEDURE  IF  EXISTS  mobile_series1;
CREATE  PROCEDURE  mobile_series1(IN  str text)   
 LANGUAGE SQL READS SQL DATA
 BEGIN 
  DROP  TABLE  IF  EXISTS  input_data1;   
  CREATE  TEMPORARYTABLE  input_data1(mobile varchar(1000))  engine=memory;

  INSERT  INTO  input_data1(mobile)  VALUES  (str);
 SELECT * FROM input_data1;
  END  |
DELIMITER;|


|Dynamic SQL requires PREPARE, see the manual page for that cmd.

PB


|

|



|

--
Best Regards
Amit Jain
www.netcore.co.in
Ext - 4109
#9833777592


Banner plane



netCORE wins GOLD in 1^st Smarties India 2012 Awards
Organized by Mobile Marketing Association (MMA)
Cross Media Integration for 'Colgate Active Salt Taste Challenge 2012' 
Campaign

*Campaigns nominated for other categories *
*Brand Awareness* Active Wheel Mobile Marketing Campaign 	*Innovation* 
Active Wheel Mobile Marketing Campaign

Re: Possible to copy the key field to another on INSERT?

[W. D.]

At 02:44 10/17/2012, Claudio Nanni wrote:
>Take a look at TRIGGERS
>
>C.

Thanks Claudio.

I wrote a trigger that MySQL accepted.  However, when
I tried to insert a new record:

  "Can't update table 'tbl' in stored function/trigger 
   because it is already used by statement which invoked 
   this stored function/trigger"

I Googled the error:

   http://dev.mysql.com/doc/refman/5.5/en/faqs-triggers.html#qandaitem-B-5-1-9

   "B.5.9: Can triggers access tables?"

   "A trigger can access both old and new data in its own 
   table.  A trigger can also affect other tables, but it 
   is not permitted to modify a table that is already 
   being used (for reading or writing) by the statement 
   that invoked the function or trigger."

From this it seems that a trigger will not allow me to 
auto-populate the `AssociatedWith` field the value of
the `KeyField`.

It looks like I will have to populate that field immediately
after creating the record with an additional statement, yes?

Does anyone have any other ideas on how to populate the
value of one field with the value of the `KeyField' on
creation(INSERT)?

I want to be able to search on the `AssociatedWith` field
to find all records that rely on that first record's 
`KeyField`.





>
>PS: I am curious to know why you would do that anyway
>
>2012/10/17 W. D. 
>
>> When creating a record, the first field (KeyField)...
>>
>>   KeyFieldBIGINT UNSIGNED NOT NULL AUTO_INCREMENT
>>
>> ...is it possible to copy this auto-generated value into
>> another field when using the same INSERT that creates the record?
>>
>> Or would I have to use an UPDATE query using LAST_INSERT_ID()
>> immediately after the INSERT statement?
>>
>> Thanks for any ideas you have.











Start Here to Find It Fast!™ -> http://www.US-Webmasters.com/best-start-page/
$9.99 Domain Names -> http://domains.us-webmasters.com/


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Possible to copy the key field to another on INSERT?

[W. D.]

At 02:44 10/17/2012, Claudio Nanni, wrote:
>Take a look at TRIGGERS
>
>C.
>
>PS: I am curious to know why you would do that anyway

Will want this 'AssociatedWith' field to be associated
with an older records' KeyField so I can search for a
group of records by this field. 







Start Here to Find It Fast!™ -> http://www.US-Webmasters.com/best-start-page/
$8.99 Domain Names -> http://domains.us-webmasters.com/


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Possible to copy the key field to another on INSERT?

[Dehua Yang]

Hello  Shawn

Thanks for your tips. We send the " select   LAST_INSERT_ID() " by our data
middle ware.

I'll ask the middle ware team to check it out.



On Wed, Oct 17, 2012 at 9:33 PM, Shawn Green wrote:

> Hello Dehua,
>
>
> On 10/17/2012 3:33 AM, Dehua Yang wrote:
>
>> select   LAST_INSERT_ID() ; Under high concurrency  , it would return
>> other
>> threads value to you.
>>
>>
> Incorrect. The results of LAST_INSERT_ID() are connection-specific. The
> activity on other connections will not change or alter the value for the
> current connection.  Only actions taken by the current connection can
> change this value.
>
> http://dev.mysql.com/doc/**refman/5.5/en/information-**
> functions.html#function_last-**insert-id<http://dev.mysql.com/doc/refman/5.5/en/information-functions.html#function_last-insert-id>
>
>
> --
> Shawn Green
> MySQL Principal Technical Support Engineer
> Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
> Office: Blountville, TN
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql
>
>


-- 
B.rgds / Whitepoplar (杨德华）

Re: Possible to copy the key field to another on INSERT?

[Shawn Green]

Hello Dehua,

On 10/17/2012 3:33 AM, Dehua Yang wrote:

select   LAST_INSERT_ID() ; Under high concurrency  , it would return other
threads value to you.



Incorrect. The results of LAST_INSERT_ID() are connection-specific. The 
activity on other connections will not change or alter the value for the 
current connection.  Only actions taken by the current connection can 
change this value.


http://dev.mysql.com/doc/refman/5.5/en/information-functions.html#function_last-insert-id


--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Possible to copy the key field to another on INSERT?

[Claudio Nanni]

Take a look at TRIGGERS

C.

PS: I am curious to know why you would do that anyway

2012/10/17 W. D. 

> When creating a record, the first field (KeyField)...
>
>   KeyFieldBIGINT UNSIGNED NOT NULL AUTO_INCREMENT
>
> ...is it possible to copy this auto-generated value into
> another field when using the same INSERT that creates the record?
>
> Or would I have to use an UPDATE query using LAST_INSERT_ID()
> immediately after the INSERT statement?
>
> Thanks for any ideas you have.
>
> Start Here to Find It Fast!™ ->
> http://www.US-Webmasters.com/best-start-page/
> $8.77 Domain Names -> http://domains.us-webmasters.com/
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql
>
>


-- 
Claudio

Re: Possible to copy the key field to another on INSERT?

[Dehua Yang]

select   LAST_INSERT_ID() ; Under high concurrency  , it would return other
threads value to you.




On Wed, Oct 17, 2012 at 2:23 PM, W. D.  wrote:

> When creating a record, the first field (KeyField)...
>
>   KeyFieldBIGINT UNSIGNED NOT NULL AUTO_INCREMENT
>
> ...is it possible to copy this auto-generated value into
> another field when using the same INSERT that creates the record?
>
> Or would I have to use an UPDATE query using LAST_INSERT_ID()
> immediately after the INSERT statement?
>
> Thanks for any ideas you have.
>
> Start Here to Find It Fast!™ ->
> http://www.US-Webmasters.com/best-start-page/
> $8.77 Domain Names -> http://domains.us-webmasters.com/
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql
>
>


-- 
B.rgds / Whitepoplar (杨德华）

Possible to copy the key field to another on INSERT?

[W. D.]

When creating a record, the first field (KeyField)...

  KeyFieldBIGINT UNSIGNED NOT NULL AUTO_INCREMENT

...is it possible to copy this auto-generated value into
another field when using the same INSERT that creates the record?

Or would I have to use an UPDATE query using LAST_INSERT_ID()
immediately after the INSERT statement?

Thanks for any ideas you have.

Start Here to Find It Fast!™ -> http://www.US-Webmasters.com/best-start-page/
$8.77 Domain Names -> http://domains.us-webmasters.com/


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Which Database when lot of insert / update queries to execute

[hsv]

 2012/06/15 18:14 +0900, Tsubasa Tanaka 
try to use `LOAD DATA INFILE' to import from CSV file.

http://dev.mysql.com/doc/refman/5.5/en/load-data.html

"Try" is the operative word: MySQL s character format is _like_ CSV, but not 
the same. The treatment of NULL is doubtless the biggest stumbling-block.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

RE: Which Database when lot of insert / update queries to execute

[Rick James]

Let's see
SHOW CREATE TABLE ...
SELECT ...

It sounds doable with MySQL; might be too big for NOSQL.

> -Original Message-
> From: abhishek jain [mailto:abhishek.netj...@gmail.com]
> Sent: Friday, June 15, 2012 1:57 AM
> To: mysql@lists.mysql.com
> Subject: Which Database when lot of insert / update queries to execute
> 
> hi,
> I am biased on mysql, and hence i am asking this on mysql forum first.
> I am designing a solution which will need me to import from CSV, i am
> using my JAVA code to parse. CSV file has 500K rows, and i need to do
> it thrice an hour, for 10 hours  a day.
> The Queries will mainly be update but select and insert also at times,
> The database size will be estimated to be about 5GB.
> I need to know is this a classic case for a NOSQL database or mysql is
> a good option.
> 
> Also , if i need to do 'group by', on a column on a large table what
> should i keep in mind, is it advisable,
> 
> Please advice,
> 
> --
> Thanks and kind Regards,
> Abhishek jain

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Which Database when lot of insert / update queries to execute

[Tsubasa Tanaka]

Hello,

> I am designing a solution which will need me to import from CSV, i am using
> my JAVA code to parse. CSV file has 500K rows, and i need to do it thrice
> an hour, for 10 hours  a day.
try to use `LOAD DATA INFILE' to import from CSV file.

http://dev.mysql.com/doc/refman/5.5/en/load-data.html


> Also , if i need to do 'group by', on a column on a large table what should
> i keep in mind, is it advisable,

create index on columns used by 'group by' and columns used by
aggregate functions.


regards,

2012/6/15 abhishek jain :
> hi,
> I am biased on mysql, and hence i am asking this on mysql forum first.
> I am designing a solution which will need me to import from CSV, i am using
> my JAVA code to parse. CSV file has 500K rows, and i need to do it thrice
> an hour, for 10 hours  a day.
> The Queries will mainly be update but select and insert also at times,
> The database size will be estimated to be about 5GB.
> I need to know is this a classic case for a NOSQL database or mysql is a
> good option.
>
> Also , if i need to do 'group by', on a column on a large table what should
> i keep in mind, is it advisable,
>
> Please advice,
>
> --
> Thanks and kind Regards,
> Abhishek jain

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Which Database when lot of insert / update queries to execute

[abhishek jain]

hi,
I am biased on mysql, and hence i am asking this on mysql forum first.
I am designing a solution which will need me to import from CSV, i am using
my JAVA code to parse. CSV file has 500K rows, and i need to do it thrice
an hour, for 10 hours  a day.
The Queries will mainly be update but select and insert also at times,
The database size will be estimated to be about 5GB.
I need to know is this a classic case for a NOSQL database or mysql is a
good option.

Also , if i need to do 'group by', on a column on a large table what should
i keep in mind, is it advisable,

Please advice,

-- 
Thanks and kind Regards,
Abhishek jain

Re: Mysql is toying me... why sometimes an insert or update can be slow!? I getting bald cuz this

[Ananda Kumar]

I used to have these issues in mysql version 5.0.41.



On Mon, May 14, 2012 at 8:13 PM, Johan De Meersman wrote:

> - Original Message -
> > From: "Ananda Kumar" 
> >
> > If numeric, then why are u using quotes. With quotes, mysql will
> > ignore the index and do a full table scan
>
> Will it? Common sense dictates that it would convert to the column's
> native type before comparing; and a quick explain seems to confirm this.
>
> That being said, it *is* better to use the actual column type from the
> start, simply to avoid the cost of implicit conversions.
>
>
> mysql> desc user;
>
> +-+--+--+-+---+-+
> | Field   | Type | Null | Key | Default   | Extra
>   |
>
> +-+--+--+-+---+-+
> | id  | int(11)  | NO   | PRI | NULL  |
> auto_increment  |
> [...]
> 16 rows in set (0.04 sec)
>
> mysql> explain select * from user where id = 1;
>
> ++-+---+---+---+-+-+---+--+---+
> | id | select_type | table | type  | possible_keys | key |
> key_len | ref   | rows | Extra |
>
> ++-+---+---+---+-+-+---+--+---+
> |  1 | SIMPLE  |  user | const | PRIMARY   | PRIMARY |
> 4   | const |1 |   |
>
> ++-+---+---+---+-+-+---+--+---+
> 1 row in set (0.07 sec)
>
> mysql> explain select * from user where id = '1';
>
> ++-+---+---+---+-+-+---+--+---+
> | id | select_type | table | type  | possible_keys | key |
> key_len | ref   | rows | Extra |
>
> ++-+---+---+---+-+-+---+--+---+
> |  1 | SIMPLE  |  user | const | PRIMARY   | PRIMARY |
> 4   | const |1 |   |
>
> ++-+---+---+---+-+-+---+--+---+
> 1 row in set (0.00 sec)
>
>
> --
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel
>

Re: Mysql is toying me... why sometimes an insert or update can be slow!? I getting bald cuz this

[Johan De Meersman]

- Original Message -
> From: "Ananda Kumar" 
> 
> If numeric, then why are u using quotes. With quotes, mysql will
> ignore the index and do a full table scan

Will it? Common sense dictates that it would convert to the column's native 
type before comparing; and a quick explain seems to confirm this.

That being said, it *is* better to use the actual column type from the start, 
simply to avoid the cost of implicit conversions.


mysql> desc user;
+-+--+--+-+---+-+
| Field   | Type | Null | Key | Default   | Extra   
|
+-+--+--+-+---+-+
| id  | int(11)  | NO   | PRI | NULL  | 
auto_increment  |
[...]
16 rows in set (0.04 sec)

mysql> explain select * from user where id = 1;
++-+---+---+---+-+-+---+--+---+
| id | select_type | table | type  | possible_keys | key | 
key_len | ref   | rows | Extra |
++-+---+---+---+-+-+---+--+---+
|  1 | SIMPLE  |  user | const | PRIMARY   | PRIMARY | 4
   | const |1 |   |
++-+---+---+---+-+-+---+--+---+
1 row in set (0.07 sec)

mysql> explain select * from user where id = '1';
++-+---+---+---+-+-+---+--+---+
| id | select_type | table | type  | possible_keys | key | 
key_len | ref   | rows | Extra |
++-+---+---+---+-+-+---+--+---+
|  1 | SIMPLE  |  user | const | PRIMARY   | PRIMARY | 4
   | const |1 |   |
++-+---+---+---+-+-+---+--+---+
1 row in set (0.00 sec)


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Mysql is toying me... why sometimes an insert or update can be slow!? I getting bald cuz this

[Ananda Kumar]

If numeric, then why are u using quotes. With quotes, mysql will ignore the
index and do a full table scan

On Mon, May 14, 2012 at 7:31 PM, Andrés Tello  wrote:

>
>
> Yes, I'm using indexes, accountid is the primary key, and is numeric and
> autoincrement.  The process doing the deadlock is no longer done...
>
> The structure of the inserted database has changed.
> Originaly it was a single table with 219millions rows, now I partitioned
> the hable in... 60 tables, 1 for each month for 5 years..
>
> I gain a lot of speed with the partitioning and sql adjustments, but I
> think I lost a lot of speed at the insert...
>
> The database stats are like 95% reading 5% writting...but that 5% is
> mostly done in a batch process who happends to be done inside a time
> window...
>
> I know I need to do more profiling, but... at least for now dev team is
> updating the batch process from long secuencial process with huge slow
> inserts, to small parallel task with burst of inserts...
>
>
>
>
> On Mon, May 14, 2012 at 8:18 AM, Ananda Kumar  wrote:
>
>> is accountid a number or varchar column
>>
>>
>> On Sat, May 12, 2012 at 7:38 PM, Andrés Tello wrote:
>>
>>> While doning a batch process...
>>>
>>> show full processlist show:
>>>
>>> | 544 | prod | 90.0.0.51:51262 | tmz2012 | Query   |6 |
>>> end  | update `account` set `balance`= 0.00 +
>>> '-4000' where accountid='2583092'
>>>
>>> No other process, lo locking no nothing...
>>>
>>> so you take this same query... run it isolated, and the mufu... is
>>> just...f fast!
>>>
>>>
>>> update `account` set `balance`= 0.00 + '-4000' where accountid='2583092'
>>> Query OK, 0 rows affected (0.00 sec)
>>> Rows matched: 1  Changed: 0  Warnings: 0
>>>
>>>
>>> ARRRG!  I have seen this type of query take as long as 100+ seconds..
>>> and I
>>> don't have a F*** clue...
>>>
>>
>>
>

Re: Mysql is toying me... why sometimes an insert or update can be slow!? I getting bald cuz this

[Andrés Tello]

Yes, I'm using indexes, accountid is the primary key, and is numeric and
autoincrement.  The process doing the deadlock is no longer done...

The structure of the inserted database has changed.
Originaly it was a single table with 219millions rows, now I partitioned
the hable in... 60 tables, 1 for each month for 5 years..

I gain a lot of speed with the partitioning and sql adjustments, but I
think I lost a lot of speed at the insert...

The database stats are like 95% reading 5% writting...but that 5% is mostly
done in a batch process who happends to be done inside a time window...

I know I need to do more profiling, but... at least for now dev team is
updating the batch process from long secuencial process with huge slow
inserts, to small parallel task with burst of inserts...




On Mon, May 14, 2012 at 8:18 AM, Ananda Kumar  wrote:

> is accountid a number or varchar column
>
>
> On Sat, May 12, 2012 at 7:38 PM, Andrés Tello wrote:
>
>> While doning a batch process...
>>
>> show full processlist show:
>>
>> | 544 | prod | 90.0.0.51:51262 | tmz2012 | Query   |6 |
>> end  | update `account` set `balance`= 0.00 +
>> '-4000' where accountid='2583092'
>>
>> No other process, lo locking no nothing...
>>
>> so you take this same query... run it isolated, and the mufu... is
>> just...f fast!
>>
>>
>> update `account` set `balance`= 0.00 + '-4000' where accountid='2583092'
>> Query OK, 0 rows affected (0.00 sec)
>> Rows matched: 1  Changed: 0  Warnings: 0
>>
>>
>> ARRRG!  I have seen this type of query take as long as 100+ seconds.. and
>> I
>> don't have a F*** clue...
>>
>
>

Re: Mysql is toying me... why sometimes an insert or update can be slow!? I getting bald cuz this

[Ananda Kumar]

is accountid a number or varchar column

On Sat, May 12, 2012 at 7:38 PM, Andrés Tello  wrote:

> While doning a batch process...
>
> show full processlist show:
>
> | 544 | prod | 90.0.0.51:51262 | tmz2012 | Query   |6 |
> end  | update `account` set `balance`= 0.00 +
> '-4000' where accountid='2583092'
>
> No other process, lo locking no nothing...
>
> so you take this same query... run it isolated, and the mufu... is
> just...f fast!
>
>
> update `account` set `balance`= 0.00 + '-4000' where accountid='2583092'
> Query OK, 0 rows affected (0.00 sec)
> Rows matched: 1  Changed: 0  Warnings: 0
>
>
> ARRRG!  I have seen this type of query take as long as 100+ seconds.. and I
> don't have a F*** clue...
>

RE: Mysql is toying me... why sometimes an insert or update can be slow!? I getting bald cuz this

[Steven Staples]

> -Original Message-
> From: Andrés Tello [mailto:mr.crip...@gmail.com]
> Sent: May 12, 2012 10:08 AM
> To: mysql
> Subject: Mysql is toying me... why sometimes an insert or update can be
> slow!? I getting bald cuz this
> 
> While doning a batch process...
> 
> show full processlist show:
> 
> | 544 | prod | 90.0.0.51:51262 | tmz2012 | Query   |6 |
> end  | update `account` set `balance`= 0.00 +
> '-4000' where accountid='2583092'
> 
> No other process, lo locking no nothing...
> 
> so you take this same query... run it isolated, and the mufu... is
> just...f fast!
> 
> 
> update `account` set `balance`= 0.00 + '-4000' where accountid='2583092'
> Query OK, 0 rows affected (0.00 sec)
> Rows matched: 1  Changed: 0  Warnings: 0
> 
> 
> ARRRG!  I have seen this type of query take as long as 100+ seconds.. and
I
> don't have a F*** clue...
> 

2 things come to mind here...

1)  indexes could not be set, so it may cause huge slowdowns on bigger
tables

2)  it could have been fast because of query caching, so it may have cached
the "index" you were updating for, which may make it work fast.  Also, if
you run it manually, the first time may be slow, and subsequent attempts are
faster (again, caching)

I may be wrong here, but something to check out.

Try this:

Explain SELECT * FROM `account` WHERE accountid='2583092';

That will tell you what indexes it is using, if any


Steve


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Mysql is toying me... why sometimes an insert or update can be slow!? I getting bald cuz this

[Andrés Tello]

While doning a batch process...

show full processlist show:

| 544 | prod | 90.0.0.51:51262 | tmz2012 | Query   |6 |
end  | update `account` set `balance`= 0.00 +
'-4000' where accountid='2583092'

No other process, lo locking no nothing...

so you take this same query... run it isolated, and the mufu... is
just...f fast!


update `account` set `balance`= 0.00 + '-4000' where accountid='2583092'
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0


ARRRG!  I have seen this type of query take as long as 100+ seconds.. and I
don't have a F*** clue...

Re: Large insert query gives MySQL server gone away

[Reindl Harald]

you must know how big your data are

or take a testserver and play around with increase the
value step for step - we are using 200M because it
is only relevant for import dumps here what does not
happen parallel and there is enough memory

Am 12.10.2011 15:40, schrieb Tompkins Neil:
> But how do I calculate such size ?
> 
> On Wed, Oct 12, 2011 at 2:38 PM, Reindl Harald  <mailto:h.rei...@thelounge.net>> wrote:
> 
> as big as you largest query
> 
> Am 12.10.2011 15:30, schrieb Tompkins Neil:
> > Do you have any recommendations as to what size this should be 
> increased to
> > ?
> >
> > On Tue, Oct 11, 2011 at 12:23 AM, Angela liu  <mailto:yyll2...@yahoo.com>> wrote:
> >
> >> Yeah, I think adjusting max packet size may be helpful, remember change
> >> that value in both server and client
> >>
> >> --
> >> *From:* Johnny Withers  <mailto:joh...@pixelated.net>>
> >> *To:* Neil Tompkins  <mailto:neil.tompk...@googlemail.com>>
> >> *Cc:* [MySQL] mailto:mysql@lists.mysql.com>>
> >> *Sent:* Monday, October 10, 2011 4:13 PM
> >> *Subject:* Re: Fwd: Large insert query gives MySQL server gone away
> >>
> >> Max packet size?
> >>
> >> On Oct 10, 2011 6:12 PM, "Neil Tompkins"  <mailto:neil.tompk...@googlemail.com>>
> >> wrote:
> >>
> >>
> >>> As per the subject we've a large insert query that gives up the error
> >> MySQL server has gone away when we try to execute it.  Any ideas why ?
> >>
> >> --
> >> MySQL General Mailing List
> >> For list archives: http://lists.mysql.com/mysql
> >> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=joh...@pixelated.net
> >>
> >>
> >>
> >
> 
> --
> 
> Mit besten Grüßen, Reindl Harald
> the lounge interactive design GmbH
> A-1060 Vienna, Hofmühlgasse 17
> CTO / software-development / cms-solutions
> p: +43 (1) 595 3999 33 , m: +43 
> (676) 40 221 40
> 
> icq: 154546673, http://www.thelounge.net/
> 
> http://www.thelounge.net/signature.asc.what.htm
> 
> 

-- 

Mit besten Grüßen, Reindl Harald
the lounge interactive design GmbH
A-1060 Vienna, Hofmühlgasse 17
CTO / software-development / cms-solutions
p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40
icq: 154546673, http://www.thelounge.net/

http://www.thelounge.net/signature.asc.what.htm



signature.asc
Description: OpenPGP digital signature

Re: Large insert query gives MySQL server gone away

[Tompkins Neil]

But how do I calculate such size ?

On Wed, Oct 12, 2011 at 2:38 PM, Reindl Harald wrote:

> as big as you largest query
>
> Am 12.10.2011 15:30, schrieb Tompkins Neil:
> > Do you have any recommendations as to what size this should be increased
> to
> > ?
> >
> > On Tue, Oct 11, 2011 at 12:23 AM, Angela liu  wrote:
> >
> >> Yeah, I think adjusting max packet size may be helpful, remember change
> >> that value in both server and client
> >>
> >> --
> >> *From:* Johnny Withers 
> >> *To:* Neil Tompkins 
> >> *Cc:* [MySQL] 
> >> *Sent:* Monday, October 10, 2011 4:13 PM
> >> *Subject:* Re: Fwd: Large insert query gives MySQL server gone away
> >>
> >> Max packet size?
> >>
> >> On Oct 10, 2011 6:12 PM, "Neil Tompkins" 
> >> wrote:
> >>
> >>
> >>> As per the subject we've a large insert query that gives up the error
> >> MySQL server has gone away when we try to execute it.  Any ideas why ?
> >>
> >> --
> >> MySQL General Mailing List
> >> For list archives: http://lists.mysql.com/mysql
> >> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=joh...@pixelated.net
> >>
> >>
> >>
> >
>
> --
>
> Mit besten Grüßen, Reindl Harald
> the lounge interactive design GmbH
> A-1060 Vienna, Hofmühlgasse 17
> CTO / software-development / cms-solutions
> p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40
> icq: 154546673, http://www.thelounge.net/
>
> http://www.thelounge.net/signature.asc.what.htm
>
>

Re: Large insert query gives MySQL server gone away

[Reindl Harald]

as big as you largest query

Am 12.10.2011 15:30, schrieb Tompkins Neil:
> Do you have any recommendations as to what size this should be increased to
> ?
> 
> On Tue, Oct 11, 2011 at 12:23 AM, Angela liu  wrote:
> 
>> Yeah, I think adjusting max packet size may be helpful, remember change
>> that value in both server and client
>>
>> --
>> *From:* Johnny Withers 
>> *To:* Neil Tompkins 
>> *Cc:* [MySQL] 
>> *Sent:* Monday, October 10, 2011 4:13 PM
>> *Subject:* Re: Fwd: Large insert query gives MySQL server gone away
>>
>> Max packet size?
>>
>> On Oct 10, 2011 6:12 PM, "Neil Tompkins" 
>> wrote:
>>
>>
>>> As per the subject we've a large insert query that gives up the error
>> MySQL server has gone away when we try to execute it.  Any ideas why ?
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:http://lists.mysql.com/mysql?unsub=joh...@pixelated.net
>>
>>
>>
> 

-- 

Mit besten Grüßen, Reindl Harald
the lounge interactive design GmbH
A-1060 Vienna, Hofmühlgasse 17
CTO / software-development / cms-solutions
p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40
icq: 154546673, http://www.thelounge.net/

http://www.thelounge.net/signature.asc.what.htm



signature.asc
Description: OpenPGP digital signature

Re: Fwd: Large insert query gives MySQL server gone away

[Tompkins Neil]

Do you have any recommendations as to what size this should be increased to
?

On Tue, Oct 11, 2011 at 12:23 AM, Angela liu  wrote:

> Yeah, I think adjusting max packet size may be helpful, remember change
> that value in both server and client
>
> --
> *From:* Johnny Withers 
> *To:* Neil Tompkins 
> *Cc:* [MySQL] 
> *Sent:* Monday, October 10, 2011 4:13 PM
> *Subject:* Re: Fwd: Large insert query gives MySQL server gone away
>
> Max packet size?
>
> On Oct 10, 2011 6:12 PM, "Neil Tompkins" 
> wrote:
>
>
> > As per the subject we've a large insert query that gives up the error
> MySQL server has gone away when we try to execute it.  Any ideas why ?
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql?unsub=joh...@pixelated.net
>
>
>

Re: Fwd: Large insert query gives MySQL server gone away

[Angela liu]

Yeah, I think adjusting max packet size may be helpful, remember change that 
value in both server and client



From: Johnny Withers 
To: Neil Tompkins 
Cc: [MySQL] 
Sent: Monday, October 10, 2011 4:13 PM
Subject: Re: Fwd: Large insert query gives MySQL server gone away

Max packet size?

On Oct 10, 2011 6:12 PM, "Neil Tompkins" 
wrote:


> As per the subject we've a large insert query that gives up the error
MySQL server has gone away when we try to execute it.  Any ideas why ?

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=joh...@pixelated.net

Re: Fwd: Large insert query gives MySQL server gone away

[Johnny Withers]

Max packet size?

On Oct 10, 2011 6:12 PM, "Neil Tompkins" 
wrote:


> As per the subject we've a large insert query that gives up the error
MySQL server has gone away when we try to execute it.  Any ideas why ?

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=joh...@pixelated.net

Fwd: Large insert query gives MySQL server gone away

[Neil Tompkins]

> As per the subject we've a large insert query that gives up the error MySQL 
> server has gone away when we try to execute it.  Any ideas why ?

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP

[Reindl Harald]

Am 20.09.2011 01:23, schrieb Dotan Cohen:
> On Tue, Sep 20, 2011 at 01:48, Reindl Harald  wrote:
>> i would use a samll class holding the db-connection with 
>> insert/update-methods
>> pass the whole record-array, lokk what field types are used in the table
>> and use intval(), doubleval() or mysql_real_escape-String
>>
> By the way, the database connection is include()ed from a file outside
> the webroot. This way if Apache is ever compromised or for whatever
> reason stops parsing the PHP, the resulting code returned to the
> browser won't have the daabase info (especially the password)

if stops parsing - yes, but not relevant if it is in a include
if the machine is compromised it does not matter
someone could read your files can read also the include outside the docroot



signature.asc
Description: OpenPGP digital signature

Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP

[Dotan Cohen]

On Tue, Sep 20, 2011 at 01:48, Reindl Harald  wrote:
> i would use a samll class holding the db-connection with insert/update-methods
> pass the whole record-array, lokk what field types are used in the table
> and use intval(), doubleval() or mysql_real_escape-String
>

By the way, the database connection is include()ed from a file outside
the webroot. This way if Apache is ever compromised or for whatever
reason stops parsing the PHP, the resulting code returned to the
browser won't have the daabase info (especially the password).

-- 
Dotan Cohen

http://gibberish.co.il
http://what-is-what.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP

[Dotan Cohen]

On Tue, Sep 20, 2011 at 01:48, Reindl Harald  wrote:
> i would use a samll class holding the db-connection with insert/update-methods
> pass the whole record-array, lokk what field types are used in the table
> and use intval(), doubleval() or mysql_real_escape-String
>
> so you never write "insert into" inline and if the function is well desigend 
> you
> can throw the whole $_POST to it without thinikng about datatypes and ignore
> automatically hidden-fields which are not used in the database
>
> having as simple class with $db->fetch_all(), $db->insert, $db->update
> has also the benefit that you can easy switch between mysql/mysqli
> without the big overhead of a whole abstraction-layer and extend
> this class with often used methods to make development faster
> and much more stable as dealing the whole time with inline code
>
> a basic class is written in few hours and can be extended whenever
> needed - i wrote one ten years ago and heavily use it these days
> as all the years
>
> public function insert($table, array $data)
> {
>  // so here you know where to look for fieldnames/fieldtypes
>  // prepare the data aray with escaping/intval()/doubleval()
>  // and generate finally the insert
>  //
>  // as return value use 0 on errors or the insert-id
> }
>
>

You are right, using a class has many benefits. I might do that on a
future project. Thanks.

-- 
Dotan Cohen

http://gibberish.co.il
http://what-is-what.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP

[Dotan Cohen]

On Tue, Sep 20, 2011 at 02:09, Hank  wrote:
>>
>> I want to be sure that all variables in the query are escaped. I don't
>> trust myself or anyone else to do this to every variable right before
>> the query:
>> $someVar=mysql_real_escape_string($someVar);
>>
>
> But you're doing exactly that right before the query anyway with:
> $M[username]=mysql_real_escape_string($username);
> You're just complicating things with the addition of an unneeded array.  It
> seems much simpler and less cluttered to just do:
>           $someVar=mysql_real_escape_string($someVar);
> before your insert.  All you are doing is changing "$someVar" to "$M[...]"
> and then using $M[...] in the query.  I really don't see the difference or
> benefit of using your array here.  Both methods are doing exactly the same
> thing, except one is more convoluted.

I know that this has been escaped:
$query="INSERT INTO table (username) VALUES ('{$M[username]}')";

This, I don't know if it has been escaped or not:
$query="INSERT INTO table (username) VALUES ('{$username}')";


> Now on the other hand, if you have several elements in the array $M to be
> inserted, and have a function like this to escape them all at once:
> for each ($M as &$val)  $val= mysql_real_escape_string($val);
> then your method starts to make more sense.

I could foreach it. Or not. It doesn't matter. The point is having
known-safe variables being used in the query, which are also easy to
read.

-- 
Dotan Cohen

http://gibberish.co.il
http://what-is-what.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP

[Hank]

>
>
> I want to be sure that all variables in the query are escaped. I don't
> trust myself or anyone else to do this to every variable right before
> the query:
> $someVar=mysql_real_escape_string($someVar);
>
>
But you're doing exactly that right before the query anyway with:

$M[username]=mysql_real_escape_string($username);

You're just complicating things with the addition of an unneeded array.  It
seems much simpler and less cluttered to just do:
  $someVar=mysql_real_escape_string($someVar);
before your insert.  All you are doing is changing "$someVar" to "$M[...]"
and then using $M[...] in the query.  I really don't see the difference or
benefit of using your array here.  Both methods are doing exactly the same
thing, except one is more convoluted.

Now on the other hand, if you have several elements in the array $M to be
inserted, and have a function like this to escape them all at once:

for each ($M as &$val)  $val= mysql_real_escape_string($val);

then your method starts to make more sense.

-Hank

Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP

[Reindl Harald]

Am 20.09.2011 00:39, schrieb Dotan Cohen:
> On Tue, Sep 20, 2011 at 01:11, Hank  wrote:
>> Best of both worlds:
>>> $username=$_POST['username'];
>>> // do some stuff with username here
>>> $M=array();  // Array of things to be inserted into MySQL
>>> $M[username]=mysql_real_escape_string($username); // Everything that
>>> goes into $M is escaped
>>> $query="INSERT INTO table (username) VALUES ('{$M[username]}')";
>>>
>>>
>> I'm not sure I'm seeing why, in particular, you are using an array here?
>>
> 
> I want to be sure that all variables in the query are escaped. I don't
> trust myself or anyone else to do this to every variable right before
> the query:
> $someVar=mysql_real_escape_string($someVar);
> 
> Furthermore, I don't want to clutter the query with
> mysql_real_escape_string() all over the place. Therefore, I escape
> everything before it goes into the array, so I know that all the data
> in the array have been escaped. I can then use the array members in
> the query

i would use a samll class holding the db-connection with insert/update-methods
pass the whole record-array, lokk what field types are used in the table
and use intval(), doubleval() or mysql_real_escape-String

so you never write "insert into" inline and if the function is well desigend you
can throw the whole $_POST to it without thinikng about datatypes and ignore
automatically hidden-fields which are not used in the database

having as simple class with $db->fetch_all(), $db->insert, $db->update
has also the benefit that you can easy switch between mysql/mysqli
without the big overhead of a whole abstraction-layer and extend
this class with often used methods to make development faster
and much more stable as dealing the whole time with inline code

a basic class is written in few hours and can be extended whenever
needed - i wrote one ten years ago and heavily use it these days
as all the years

public function insert($table, array $data)
{
 // so here you know where to look for fieldnames/fieldtypes
 // prepare the data aray with escaping/intval()/doubleval()
 // and generate finally the insert
 //
 // as return value use 0 on errors or the insert-id
}



signature.asc
Description: OpenPGP digital signature

Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP

[Dotan Cohen]

On Tue, Sep 20, 2011 at 01:11, Hank  wrote:
> Best of both worlds:
>> $username=$_POST['username'];
>> // do some stuff with username here
>> $M=array();  // Array of things to be inserted into MySQL
>> $M[username]=mysql_real_escape_string($username); // Everything that
>> goes into $M is escaped
>> $query="INSERT INTO table (username) VALUES ('{$M[username]}')";
>>
>>
> I'm not sure I'm seeing why, in particular, you are using an array here?
>

I want to be sure that all variables in the query are escaped. I don't
trust myself or anyone else to do this to every variable right before
the query:
$someVar=mysql_real_escape_string($someVar);

Furthermore, I don't want to clutter the query with
mysql_real_escape_string() all over the place. Therefore, I escape
everything before it goes into the array, so I know that all the data
in the array have been escaped. I can then use the array members in
the query.


-- 
Dotan Cohen

http://gibberish.co.il
http://what-is-what.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP

[Hank]

Best of both worlds:
> $username=$_POST['username'];
> // do some stuff with username here
> $M=array();  // Array of things to be inserted into MySQL
> $M[username]=mysql_real_escape_string($username); // Everything that
> goes into $M is escaped
> $query="INSERT INTO table (username) VALUES ('{$M[username]}')";
>
>
I'm not sure I'm seeing why, in particular, you are using an array here?

Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP

[Dotan Cohen]

On Mon, Sep 19, 2011 at 18:11, Reindl Harald  wrote:
> it is not because it is clear that it is sanitized instead hope and pray
> thousands of layers somewhere else did it - for a inline-query the best
> solution, if you are using a framework you will never have the "insert into"
> at this place!
>
> what i meant as ugly is that you are somewhere writing an inline-query and
> are not sure if it is a number or not - so it is NOT sanitized before
> because if you tell me it is you sanitze does not work if you get a 
> non-integer
> at this point and you sanitze-method has to throw the error long before
> if it is really working
>


Best of both worlds:
$username=$_POST['username'];
// do some stuff with username here
$M=array();  // Array of things to be inserted into MySQL
$M[username]=mysql_real_escape_string($username); // Everything that
goes into $M is escaped
$query="INSERT INTO table (username) VALUES ('{$M[username]}')";

The resulting SQL query is easy to read, and I know that everything is
escaped. No operations are ever to be performed on $M. I need to look
into a way of making it immutable (add and read only). I could do it
with an object but I prefer an array. Actually, an array wrapped in an
object could perform the escaping itself, making me doubly sure that
some other dev didn't forget to escape while playing with the code.

By the way, I've never gotten a godd explanation about why to wrap the
variables in PHP MySQL queries with curly brackets. I don't even
remember where I picked up the habit. Does anybody here know?


-- 
Dotan Cohen

http://gibberish.co.il
http://what-is-what.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP

[Reindl Harald]

Am 19.09.2011 16:55, schrieb Hank:
>>
>> what ugly style - if it is not numeric and you throw it to the database
>> you are one of the many with a sql-injection because if you are get
>> ivalid values until there you have done no sanitize before and do not here
>>
>>
> It's a matter of opinion.  I never said the data wasn't sanitized (it is).
>  But sometimes calculated values or bugs in PHP code end up with a null
> variable field.  I was just suggesting the choice between two errors -- one
> syntax which will generate a hard failure of the query and likely whatever
> page, or a soft logical error, which won't. In either case, I have error
> trapping to catch both types of errors and alert me to them. I prefer the
> errors to be logical ones and not syntax errors.
> 
> 
>> $sql="INSERT into table VALUES (" . (int)$id . ",'" .
> mysql_real_escape_string($val) . "')";
>> or using a abstraction-layer (simple self written class)
>> $sql="INSERT into table VALUES (" . (int)$id . ",'" .
> $db->escape_string($val) . "')";
> 
> I think what you posted is ugly "style" which makes reading the actual SQL
> in PHP code much harder to read and debug.  The data validation should take
> place elsewhere long before it gets to constructing the SQL statement.

it is not because it is clear that it is sanitized instead hope and pray
thousands of layers somewhere else did it - for a inline-query the best
solution, if you are using a framework you will never have the "insert into"
at this place!

what i meant as ugly is that you are somewhere writing an inline-query and
are not sure if it is a number or not - so it is NOT sanitized before
because if you tell me it is you sanitze does not work if you get a non-integer
at this point and you sanitze-method has to throw the error long before
if it is really working



signature.asc
Description: OpenPGP digital signature

Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP

[Hank]

>
> what ugly style - if it is not numeric and you throw it to the database
> you are one of the many with a sql-injection because if you are get
> ivalid values until there you have done no sanitize before and do not here
>
>
It's a matter of opinion.  I never said the data wasn't sanitized (it is).
 But sometimes calculated values or bugs in PHP code end up with a null
variable field.  I was just suggesting the choice between two errors -- one
syntax which will generate a hard failure of the query and likely whatever
page, or a soft logical error, which won't. In either case, I have error
trapping to catch both types of errors and alert me to them. I prefer the
errors to be logical ones and not syntax errors.


> $sql="INSERT into table VALUES (" . (int)$id . ",'" .
mysql_real_escape_string($val) . "')";
> or using a abstraction-layer (simple self written class)
> $sql="INSERT into table VALUES (" . (int)$id . ",'" .
$db->escape_string($val) . "')";

I think what you posted is ugly "style" which makes reading the actual SQL
in PHP code much harder to read and debug.  The data validation should take
place elsewhere long before it gets to constructing the SQL statement.

Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP

[Dotan Cohen]

On Mon, Sep 19, 2011 at 07:47, Reindl Harald  wrote:
> what ugly style - if it is not numeric and you throw it to the database
> you are one of the many with a sql-injection because if you are get
> ivalid values until there you have done no sanitize before and do not here
>
> $sql="INSERT into table VALUES (" . (int)$id . ",'" . 
> mysql_real_escape_string($val) . "')";
> or using a abstraction-layer (simple self written class)
> $sql="INSERT into table VALUES (" . (int)$id . ",'" . 
> $db->escape_string($val) . "')";
>
> all other things in the context of hand-written queries are all the nice one 
> we read every
> day in the news and should NOT recommended because the next beginner reading 
> this makes all
> the mistakes again
>

Thanks, Reindi. I actually do something like this (simplified, in real
code I use an array and a small custom function):
$mysqlName=mysql_real_escape_string($name);
Then, in the query I can see that all my variables start with $mysql*
so I know that they have been sanitized.

-- 
Dotan Cohen

http://gibberish.co.il
http://what-is-what.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP

[Dotan Cohen]

On Mon, Sep 19, 2011 at 04:00, Hank  wrote:
> I agree with Brandon's suggestions, I would just add when using numeric
> types in PHP statements where you have a variable replacement, for instance:
>
> $sql="INSERT into table VALUES ('$id','$val')";
>
> where $id is a numeric variable in PHP and a numeric field in the table,
> I'll include the $id in single quotes in the PHP statement, so even if the
> value of $id is null, alpha, or invalid (not numeric) it does not generate a
> mysql syntax error. Otherwise, without the single quotes, the statement
> would be:
>
> INSERT into table VALUES (,'');
>
>  which would cause a syntax error.  If you include the single quotes, it
> becomes:
>
> INSERT into table VALUES ('','')
>
> which won't cause a syntax error, but might cause some logic errors in the
> database.  The choice is yours.
>

‎Thanks, that is a good point. I would actually prefer errors to arise
on insert then a potentially inconsistent database or bad data. I
should definitely learn to use stored procedures, I know.

That said, I do go to great lengths to validate my data. What is an
"alpha" value? I do check is_numeric() and null, of course.

-- 
Dotan Cohen

http://gibberish.co.il
http://what-is-what.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP

[Reindl Harald]

Am 19.09.2011 03:00, schrieb Hank:
> I agree with Brandon's suggestions, I would just add when using numeric
> types in PHP statements where you have a variable replacement, for instance:
> 
> $sql="INSERT into table VALUES ('$id','$val')";
> 
> where $id is a numeric variable in PHP and a numeric field in the table,
> I'll include the $id in single quotes in the PHP statement, so even if the
> value of $id is null, alpha, or invalid (not numeric) it does not generate a
> mysql syntax error

what ugly style - if it is not numeric and you throw it to the database
you are one of the many with a sql-injection because if you are get
ivalid values until there you have done no sanitize before and do not here

$sql="INSERT into table VALUES (" . (int)$id . ",'" . 
mysql_real_escape_string($val) . "')";
or using a abstraction-layer (simple self written class)
$sql="INSERT into table VALUES (" . (int)$id . ",'" . $db->escape_string($val) 
. "')";

all other things in the context of hand-written queries are all the nice one we 
read every
day in the news and should NOT recommended because the next beginner reading 
this makes all
the mistakes again




signature.asc
Description: OpenPGP digital signature

Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP

[Hank]

On Sun, Sep 18, 2011 at 12:28 PM, Dotan Cohen  wrote:

> On Sun, Sep 18, 2011 at 17:44, Brandon Phelps  wrote:
> > Personally I don't use any quotes for the numeric types, and single
> quotes
> > for everything else.  Ie:
> >
>
> Thanks, Brandon. I understand then that quote type is a matter of
> taste. I always use double quotes in PHP and I've only recently
> started putting ticks around table and column names. I'll stick to
> your convention of no quotes around numerics and single quotes around
> everything else.
>
>
I agree with Brandon's suggestions, I would just add when using numeric
types in PHP statements where you have a variable replacement, for instance:

$sql="INSERT into table VALUES ('$id','$val')";

where $id is a numeric variable in PHP and a numeric field in the table,
I'll include the $id in single quotes in the PHP statement, so even if the
value of $id is null, alpha, or invalid (not numeric) it does not generate a
mysql syntax error. Otherwise, without the single quotes, the statement
would be:

INSERT into table VALUES (,'');

 which would cause a syntax error.  If you include the single quotes, it
becomes:

INSERT into table VALUES ('','')

which won't cause a syntax error, but might cause some logic errors in the
database.  The choice is yours.

Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP

[Dotan Cohen]

On Sun, Sep 18, 2011 at 17:44, Brandon Phelps  wrote:
> Personally I don't use any quotes for the numeric types, and single quotes
> for everything else.  Ie:
>

Thanks, Brandon. I understand then that quote type is a matter of
taste. I always use double quotes in PHP and I've only recently
started putting ticks around table and column names. I'll stick to
your convention of no quotes around numerics and single quotes around
everything else.

Have a terrific week!

-- 
Dotan Cohen

http://gibberish.co.il
http://what-is-what.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Quotes around INSERT and SELECT statements' arguments from the mysql CLI and PHP

[Brandon Phelps]

Personally I don't use any quotes for the numeric types, and single quotes for 
everything else.  Ie:

UPDATE mytable SET int_field = 5 WHERE id = 3;
SELECT id FROM mytable WHERE int_field = 5;
UPDATE mytable SET varchar_field = 'Test' WHERE id = 3;
SELECT id FROM mytable WHERE varchar_field = 'Test';
UPDATE mytable SET datetime_field = '2011-09-18 00:00:00' WHERE id = 3;

If you are using PHP you may need to escape the single quotes if your php 
string is in single quotes:
$query = 'UPDATE mytable SET varchar_field = \'Test\' WHERE id = 3'

But if you are doing interpolation and your string is in double quotes, you 
should not need to escape:
$query = "UPDATE $table_name SET varchar_field = 'Test' WHERE id = 3"

Some people prefer to use back quotes on field names such as:
$query = "UPDATE `mytable` SET `varchar_field` = 'Test' WHERE `id` = 3"

And some people prefer to put numeric fields in quotes as well, although it is 
not necessary:
UPDATE mytable SET int_field = '5' WHERE id = '3';

On 9/18/11 5:00 AM, Dotan Cohen wrote:

I am somewhat confused as to the proper way to place quotes around
arguments in INSERT and SELECT statements. I also don't see where this
is made explicit in the fine manual.

If the column is type int, is it preferable to use single, double, or
no quotes on INSERT from the mysql cli?
If the column is type int, is it preferable to use single, double, or
no quotes on SELECT from the mysql cli?
If the column is type int, is it preferable to use single, double, or
no quotes on INSERT from PHP?
If the column is type int, is it preferable to use single, double, or
no quotes on SELECT from PHP?
Is it the same for decimal and float?

If the column is type varchar, is it preferable to use single or
double quotes on INSERT from the mysql cli?
If the column is type varchar, is it preferable to use single or
double quotes on SELECT from the mysql cli?
If the column is type varchar, is it preferable to use single or
double quotes on INSERT from PHP?
If the column is type varchar, is it preferable to use single or
double quotes on SELECT from PHP?
Is it the same for text and blob?
Also, in PHP often I see code examples with the variable wrapped in
curly brackets, inside single quotes. What is the purpose of the curly
brackets? Here is such an example:
$query="INSERT INTO names (name) VALUE ('{$userName}')";

If the column is type datetime, is it preferable to use single or
double quotes on INSERT from the mysql cli?
If the column is type datetime, is it preferable to use single or
double quotes on SELECT from the mysql cli?
If the column is type datetime, is it preferable to use single or
double quotes on INSERT from PHP?
If the column is type datetime, is it preferable to use single or
double quotes on SELECT from PHP?
What if I am using the NOW() function?

If the column is type set, is it preferable to use single or double
quotes on INSERT from the mysql cli?
If the column is type set, is it preferable to use single or double
quotes on SELECT from the mysql cli?
If the column is type set, is it preferable to use single or double
quotes on INSERT from PHP?
If the column is type set, is it preferable to use single or double
quotes on SELECT from PHP?

Thanks.



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: stored procedure insert statement

[Igor Shevtsov]

Thanks Johnny,
In this case I wouldn't be able to insert a completely new row but
replace the existent one, so row count would stay the same.
This is a storage table with the only unique constraints on:
dda_debits_id column.
the test data is very small, so I would've noticed any duplicates and
they wouldn't make it to the table anyway with or without INSERT IGNORE.

+---+-+--+-+-++
| Field | Type| Null | Key | Default |
Extra  |
+---+-+--+-+-++
| dda_debits_id | int(11) | NO   | PRI |0
||
| created_on| datetime| YES  | | NULL   
||
| reference_number  | varchar(18) | YES  | | NULL   
||
| user_format_debit_ref | varchar(18) | YES  | | NULL   
||
| amount| int(11) | YES  | | NULL   
||
| debit_date| datetime| YES  | | NULL   
||
| status| tinyint(1)  | YES  | | NULL   
||
| debit_type| tinyint(1)  | YES  | | NULL   
||
| recharge_for_id   | int(11) | YES  | | NULL   
||
| processed_on  | datetime| YES  | | NULL   
||
| service_user_id   | int(11) | YES  | | NULL   
||
+---+-+--+-+-++

Claudio, good point.
Unfortunately, didn't work.  I tried it before but no luck.
Thanks,
Igor





On 07/09/2011 02:43 PM, Johnny Withers wrote:
>
> It seems to me that your insert statement is trying to insert
> duplicate rows into the storage table. This is why insert ignore and
> replace work.
>
>> On Jul 9, 2011 3:49 AM, "Igor Shevtsov" > <mailto:nixofort...@googlemail.com>> wrote:
>>
>> Hi all,
>> I can't explain strange behaviour of the INSERT statement in the stored
>> procedure.
>> The idea is to generate a list based on the output of 3 INNER JOIN of
>> regularly updated tables.
>> Something like :
>>
>> INSERT INTO storage
>> (column list)
>> SELECT
>> column list
>> FROM t1 JOIN t2
>> ON t1.x=t2.y
>> JOIN t3
>> ON t2.z=t3.w
>> WHERE CONDITIONS;
>>
>> The procedure runs daily by crontask and it inserts correct number of
>> output rows.
>>
>> But after It runs and populated a storage table, I added new entries and
>> expect to find them in the storage table. Even though they were picked
>> up by SELECT statement, they haven't been INSERTed into the storage
>> table.
>> If I DELETE or TRUNCATE from the storage table and run the procedure all
>> newly added entries and existed entries are their, but if I add new rows
>> and run the procedure again It doesn't update the table.
>> All tables have a unique identifier, so duplicate errors are impossible.
>> I use INNODB engine for all tables.
>> I understand that stored procedure is a precompiled thing and I believe
>> it could be something to do with cache but I couldn't find proper
>> explanation or similar case online.
>> I found when I use INSERT IGNORE INTO or INSERT REPLACE INTO, I receive
>> the proper result with newly entries added to the storage table.
>> Any ideas guys?
>> Have a nice weekend ALL.
>> Cheers,
>> Igor
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:  
>>  http://lists.mysql.com/mysql?unsub=joh...@pixelated.net
>>

Re: stored procedure insert statement

[Claudio Nanni]

That's what is bad of SP in MySQL, debugging.

Just out of the blue,
can you try to disable query cache?

*SET GLOBAL query_cache_size = 0;*

*

SET GLOBAL query_cache_type = 0;

*


it could be a bug

Claudio

2011/7/9 Johnny Withers 

> It seems to me that your insert statement is trying to insert duplicate
> rows
> into the storage table. This is why insert ignore and replace work.
>
> On Jul 9, 2011 3:49 AM, "Igor Shevtsov" 
> wrote:
>
> Hi all,
> I can't explain strange behaviour of the INSERT statement in the stored
> procedure.
> The idea is to generate a list based on the output of 3 INNER JOIN of
> regularly updated tables.
> Something like :
>
> INSERT INTO storage
> (column list)
> SELECT
> column list
> FROM t1 JOIN t2
> ON t1.x=t2.y
> JOIN t3
> ON t2.z=t3.w
> WHERE CONDITIONS;
>
> The procedure runs daily by crontask and it inserts correct number of
> output rows.
>
> But after It runs and populated a storage table, I added new entries and
> expect to find them in the storage table. Even though they were picked
> up by SELECT statement, they haven't been INSERTed into the storage table.
> If I DELETE or TRUNCATE from the storage table and run the procedure all
> newly added entries and existed entries are their, but if I add new rows
> and run the procedure again It doesn't update the table.
> All tables have a unique identifier, so duplicate errors are impossible.
> I use INNODB engine for all tables.
> I understand that stored procedure is a precompiled thing and I believe
> it could be something to do with cache but I couldn't find proper
> explanation or similar case online.
> I found when I use INSERT IGNORE INTO or INSERT REPLACE INTO, I receive
> the proper result with newly entries added to the storage table.
> Any ideas guys?
> Have a nice weekend ALL.
> Cheers,
> Igor
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql?unsub=joh...@pixelated.net
>



-- 
Claudio

Re: stored procedure insert statement

[Johnny Withers]

It seems to me that your insert statement is trying to insert duplicate rows
into the storage table. This is why insert ignore and replace work.

On Jul 9, 2011 3:49 AM, "Igor Shevtsov"  wrote:

Hi all,
I can't explain strange behaviour of the INSERT statement in the stored
procedure.
The idea is to generate a list based on the output of 3 INNER JOIN of
regularly updated tables.
Something like :

INSERT INTO storage
(column list)
SELECT
column list
FROM t1 JOIN t2
ON t1.x=t2.y
JOIN t3
ON t2.z=t3.w
WHERE CONDITIONS;

The procedure runs daily by crontask and it inserts correct number of
output rows.

But after It runs and populated a storage table, I added new entries and
expect to find them in the storage table. Even though they were picked
up by SELECT statement, they haven't been INSERTed into the storage table.
If I DELETE or TRUNCATE from the storage table and run the procedure all
newly added entries and existed entries are their, but if I add new rows
and run the procedure again It doesn't update the table.
All tables have a unique identifier, so duplicate errors are impossible.
I use INNODB engine for all tables.
I understand that stored procedure is a precompiled thing and I believe
it could be something to do with cache but I couldn't find proper
explanation or similar case online.
I found when I use INSERT IGNORE INTO or INSERT REPLACE INTO, I receive
the proper result with newly entries added to the storage table.
Any ideas guys?
Have a nice weekend ALL.
Cheers,
Igor

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=joh...@pixelated.net

stored procedure insert statement

[Igor Shevtsov]

Hi all,
I can't explain strange behaviour of the INSERT statement in the stored
procedure.
The idea is to generate a list based on the output of 3 INNER JOIN of
regularly updated tables.
Something like :

INSERT INTO storage
(column list)
SELECT
column list
FROM t1 JOIN t2
ON t1.x=t2.y
JOIN t3
ON t2.z=t3.w
WHERE CONDITIONS;

The procedure runs daily by crontask and it inserts correct number of
output rows.

But after It runs and populated a storage table, I added new entries and
expect to find them in the storage table. Even though they were picked
up by SELECT statement, they haven't been INSERTed into the storage table.
If I DELETE or TRUNCATE from the storage table and run the procedure all
newly added entries and existed entries are their, but if I add new rows
and run the procedure again It doesn't update the table.
All tables have a unique identifier, so duplicate errors are impossible.
I use INNODB engine for all tables.
I understand that stored procedure is a precompiled thing and I believe
it could be something to do with cache but I couldn't find proper
explanation or similar case online.
I found when I use INSERT IGNORE INTO or INSERT REPLACE INTO, I receive
the proper result with newly entries added to the storage table.
Any ideas guys?
Have a nice weekend ALL.
Cheers,
Igor

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: [setting value when INSERT for auto increment]

[Basil Daoust]

since your listing the columns, you could just leave off `idlog` from 
the named columns and thus not also need to include the null in the 
inserted values.


INSERT INTO `friendlyCMS`.`log`
(`imepriimek`, `clock`, `action`, `onfile`, `filesize`)
VALUES ($_COOKIE['user'], CURRENT_TIMESTAMP, 'saved', $filename, 
filesize($filename));



On 16/05/2011 9:59 AM, Johan De Meersman wrote:

If you're asking what I think you're asking, then yes, both NULL and 0 will 
trigger an autoincrement field to put in the next value.

- Original Message -

From: "Grega Leskovšek"
To: mysql@lists.mysql.com
Sent: Monday, 16 May, 2011 4:49:43 PM
Subject: [setting value when INSERT for auto increment]

Should it be null?
INSERT INTO `friendlyCMS`.`log` (`idlog`, `imepriimek`, `clock`,
`action`, `onfile`, `filesize`) VALUES (NULL, $_COOKIE['user'],
CURRENT_TIMESTAMP, 'saved',$filename, filesize($filename));
idlog is primaryk ey auto inrement not null...
When insertin the value what should I pass it? NULL? Thanks in
advance!!



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: [setting value when INSERT for auto increment]

[Johan De Meersman]

If you're asking what I think you're asking, then yes, both NULL and 0 will 
trigger an autoincrement field to put in the next value.

- Original Message -
> From: "Grega Leskovšek" 
> To: mysql@lists.mysql.com
> Sent: Monday, 16 May, 2011 4:49:43 PM
> Subject: [setting value when INSERT for auto increment]
>
> Should it be null?
> INSERT INTO `friendlyCMS`.`log` (`idlog`, `imepriimek`, `clock`,
> `action`, `onfile`, `filesize`) VALUES (NULL, $_COOKIE['user'],
> CURRENT_TIMESTAMP, 'saved',$filename, filesize($filename));
> idlog is primaryk ey auto inrement not null...
> When insertin the value what should I pass it? NULL? Thanks in
> advance!!

--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

[setting value when INSERT for auto increment]

[Grega Leskovšek]

Should it be null?
INSERT INTO `friendlyCMS`.`log` (`idlog`, `imepriimek`, `clock`,
`action`, `onfile`, `filesize`) VALUES (NULL, $_COOKIE['user'],
CURRENT_TIMESTAMP, 'saved',$filename, filesize($filename));
idlog is primaryk ey auto inrement not null...
When insertin the value what should I pass it? NULL? Thanks in advance!!
-- When the sun rises I receive and when it sets I forgive ->
http://moj.skavt.net/gleskovs/
Always in Heart, Grega Leskovšek

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: Insert data in one table from Another Problem

[Travis Ard]

Here's one option to "pivot" your results:

select
record_id
,max(soi) as soi
,max(heading) as heading
,max(description) as description
,max(relloc) as relloc
from
(select 
record_id
,if(field_name = 'SOI', field_value, '') as soi
,if(field_name = 'Heading', field_value, '') as heading
,if(field_name = 'Description', field_value, '') as description
,if(field_name = 'RelLoc', field_value, '') as relloc
from user_news) s1
group by s1.record_id;



-Original Message-
From: Adarsh Sharma [mailto:adarsh.sha...@orkash.com] 
Sent: Wednesday, February 16, 2011 6:33 AM
To: mysql@lists.mysql.com
Subject: Insert data in one table from Another Problem

 
Dear all,

Today I am puzzled around a problem of inserting data into new table in 
new format. I have a table named *user_news* as :

We have four rows with respect to each record_id.

fore.g : I have listed main columns as
*record_id   field_name   field_value*
572SOIMedia
572  Heading   A senior Police official confirmed the 
presence of the stone quarry at Jafflong near the India-Bangladesh border
572Description HNLC runs a stone quarry in Jafflong 
District of Bangladesh. The outfit is also believed to own several betel 
nut plantations besides running other business in Bangladesh.
572  RelLoc Jafflong

578SOI   Media
578 Heading   Army Chief General V. K. Singh in Shillong 
said he was confident that the NDFB would come to the negotiating table 
if they are "handled properly"
578Description   A school teacher was abducted by 
unidentified militants in Damas of East Garo Hills District. Army Chief 
General V. K. Singh in Shillong said he was confident .
578   RelLoc  Garo Hills

Similarly i have 1000 of rows.

Now I create a new table as columns as :

*record_id  SOI  heading  Description   RelLoc *  and its 
values is as :
 *  

*
572   MediaA senior Police official confirmed the 
presence of the stone quarry at Jafflong near the India-Bangladesh 
border HNLC runs a stone quarry in Jafflong District of 
Bangladesh.  Jafflong  



   

 
The values in *field_name* becomes four columns in the above table . and 
their values are the values of f*ield_value *column.

The problem is that I want this data now in horizontal form and the data 
of four rows in one row. That is four rows in one table contributes a 
single row in *other *table.

I try with procedures and cursors but fail to achieve the output.

Is it possible in Mysql. Please guide me how to achieve this as I am 
stuck around it.



Thanks & Best Regards

Adarsh Sharma













-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Insert data in one table from Another Problem

[Adarsh Sharma]

Dear all,

Today I am puzzled around a problem of inserting data into new table in 
new format. I have a table named *user_news* as :


We have four rows with respect to each record_id.

fore.g : I have listed main columns as
*record_id   field_name   field_value*
572SOIMedia
572  Heading   A senior Police official confirmed the 
presence of the stone quarry at Jafflong near the India-Bangladesh border
572Description HNLC runs a stone quarry in Jafflong 
District of Bangladesh. The outfit is also believed to own several betel 
nut plantations besides running other business in Bangladesh.

572  RelLoc Jafflong

578SOI   Media
578 Heading   Army Chief General V. K. Singh in Shillong 
said he was confident that the NDFB would come to the negotiating table 
if they are "handled properly"
578Description   A school teacher was abducted by 
unidentified militants in Damas of East Garo Hills District. Army Chief 
General V. K. Singh in Shillong said he was confident .

578   RelLoc  Garo Hills

Similarly i have 1000 of rows.

Now I create a new table as columns as :

*record_id  SOI  heading  Description   RelLoc *  and its 
values is as :
*  
   
   *
572   MediaA senior Police official confirmed the 
presence of the stone quarry at Jafflong near the India-Bangladesh 
border HNLC runs a stone quarry in Jafflong District of 
Bangladesh.  Jafflong  
   
   
   
  
   

The values in *field_name* becomes four columns in the above table . and 
their values are the values of f*ield_value *column.


The problem is that I want this data now in horizontal form and the data 
of four rows in one row. That is four rows in one table contributes a 
single row in *other *table.


I try with procedures and cursors but fail to achieve the output.

Is it possible in Mysql. Please guide me how to achieve this as I am 
stuck around it.




Thanks & Best Regards

Adarsh Sharma

Re: Insert only if the entry doesn't exist

[Joerg Bruehe]

Hi!


Andre Polykanine wrote:
> Hello Rolando,
> 
> So if I do
> "INSERT IGNORE INTO `Votes` SET `EntryId`='12345', UserId`='789'";
> it  *won't* insert the second row if there's a row with EntryId set to
> 12345 and UserId set to 789?

If you want to have at most one vote per user on any entry, IMO you
should have a "unique index" on that combination of fields.


Aside:
Assuming you "*Id" values are numeric types, you should stop using
quotes around them: quotes enclose strings.
I know MySQL implicitly converts numbers to strings and vice versa, but
this costs performance and IMO is a bad idea anyways.
I feel a programmer should know the distinction of numeric and string
types and keep them separate in a well-structured application.


HTH,
Jörg

-- 
Joerg Bruehe,  MySQL Build Team,  joerg.bru...@oracle.com
ORACLE Deutschland B.V. & Co. KG,   Komturstrasse 18a,   D-12099 Berlin
Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven
Amtsgericht Muenchen: HRA 95603


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Insert only if the entry doesn't exist

[Andre Polykanine]

Hello Rolando,

So if I do
"INSERT IGNORE INTO `Votes` SET `EntryId`='12345', UserId`='789'";
it  *won't* insert the second row if there's a row with EntryId set to
12345 and UserId set to 789?
Thanks and sorry!)

-- 
With best regards from Ukraine,
Andre
Skype: Francophile
My blog: http://oire.org/menelion (mostly in Russian)
Twitter: http://twitter.com/m_elensule
Facebook: http://facebook.com/menelion

 Original message 
From: Rolando Edwards 
To: Andre Polykanine
Date created: , 11:01:40 PM
Subject: Insert only if the entry doesn't exist


  If the table has Primary and/or UNIQUE Keys, then you are fine.

You do not need to know what they are. If you want to see them do this:
SHOW CREATE TABLE Votes\G

INSERT IGNORE INTO does not require ON DUPLICATE KEY options.
After all, you said earlier that you want to do nothing if the row exists.

Rolando A. Edwards
MySQL DBA (SCMDBA)

155 Avenue of the Americas, Fifth Floor
New York, NY 10013
212-625-5307 (Work)
201-660-3221 (Cell)
AIM & Skype : RolandoLogicWorx
redwa...@logicworks.net
http://www.linkedin.com/in/rolandoedwards


-Original Message-
From: Andre Polykanine [mailto:an...@oire.org] 
Sent: Monday, February 14, 2011 3:40 PM
To: Rolando Edwards
Cc: João Cândido de Souza Neto; mysql@lists.mysql.com
Subject: Re: Insert only if the entry doesn't exist

Hello Rolando,

Sorry,  but  if  I  do  INSERT IGNORE INTO, then I must indicate a key
(typically a unique key or a primary key), or is it false? But I don't
know that key and no way to get it without more queries...

-- 
With best regards from Ukraine,
Andre
Skype: Francophile
My blog: http://oire.org/menelion (mostly in Russian)
Twitter: http://twitter.com/m_elensule
Facebook: http://facebook.com/menelion

 Original message 
From: Rolando Edwards 
To: João Cândido de Souza Neto
Date created: , 10:33:05 PM
Subject: Insert only if the entry doesn't exist


  Be Careful. REPLACE INTO mechanically does DELETE and INSERT under 
mysqld's hood.
If you want to do nothing if row exists already then do:

INSERT IGNORE instead of REPLACE INTO

Rolando A. Edwards
MySQL DBA (SCMDBA)

155 Avenue of the Americas, Fifth Floor
New York, NY 10013
212-625-5307 (Work)
201-660-3221 (Cell)
AIM & Skype : RolandoLogicWorx
redwa...@logicworks.net
http://www.linkedin.com/in/rolandoedwards


-Original Message-
From: João Cândido de Souza Neto [mailto:j...@consultorweb.cnt.br] 
Sent: Monday, February 14, 2011 1:39 PM
To: mysql@lists.mysql.com
Subject: Re: Insert only if the entry doesn't exist

Instead of "insert into" you can use "replace into".

-- 
João Cândido de Souza Neto

"Andre Polykanine"  escreveu na mensagem 
news:1621362474.20110214201...@oire.org...
Hi all,
Thanks for your fast answer to my last question!
Here's one more problem I commonly deal with.
There  are  cases  when  I need to insert the row only if such a row
doesn't  exist,  otherwise  I  need either to update the row or to do
nothing, just skip the query.
The  common case is the rating increment/decrement. I have the + and -
links  which are AJAX. So I need to prevent fast multiple clicking and
over-increasing or over-decreasing the rating of the blog entry.
Here's what I'm doing, in a simplified form (assume all of the numbers
are PHP variables):
"SELECT `Id` FROM `Votes` WHERE `EntryId`='12345' AND `UserId`='789'";
then I'm checking if the user has already clicked a link:
if (mysql_num_rows()==0) {
"INSERT INTO `Votes` SET `EntryId`='12345', `UserId`='789'";
} else {
// do nothing
}

And here is the question (at last!):
How  can  I  MySql'ly  check whether a vote does exist and if it does,
then insert the row, else either do nothing or update several columns?
I  assume I can't use ON DUPLICATE KEY UPDATE because I have no unique
keys...
Hope you understood my problem).
Thanks!



-- 
With best regards from Ukraine,
Andre
Skype: Francophile
Twitter: http://twitter.com/m_elensule
Facebook: http://facebook.com/menelion



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=redwa...@logicworks.net


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=an...@oire.org


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: Insert only if the entry doesn't exist

[Rolando Edwards]

If the table has Primary and/or UNIQUE Keys, then you are fine.

You do not need to know what they are. If you want to see them do this:
SHOW CREATE TABLE Votes\G

INSERT IGNORE INTO does not require ON DUPLICATE KEY options.
After all, you said earlier that you want to do nothing if the row exists.

Rolando A. Edwards
MySQL DBA (SCMDBA)

155 Avenue of the Americas, Fifth Floor
New York, NY 10013
212-625-5307 (Work)
201-660-3221 (Cell)
AIM & Skype : RolandoLogicWorx
redwa...@logicworks.net
http://www.linkedin.com/in/rolandoedwards


-Original Message-
From: Andre Polykanine [mailto:an...@oire.org] 
Sent: Monday, February 14, 2011 3:40 PM
To: Rolando Edwards
Cc: João Cândido de Souza Neto; mysql@lists.mysql.com
Subject: Re: Insert only if the entry doesn't exist

Hello Rolando,

Sorry,  but  if  I  do  INSERT IGNORE INTO, then I must indicate a key
(typically a unique key or a primary key), or is it false? But I don't
know that key and no way to get it without more queries...

-- 
With best regards from Ukraine,
Andre
Skype: Francophile
My blog: http://oire.org/menelion (mostly in Russian)
Twitter: http://twitter.com/m_elensule
Facebook: http://facebook.com/menelion

 Original message 
From: Rolando Edwards 
To: João Cândido de Souza Neto
Date created: , 10:33:05 PM
Subject: Insert only if the entry doesn't exist


  Be Careful. REPLACE INTO mechanically does DELETE and INSERT under 
mysqld's hood.
If you want to do nothing if row exists already then do:

INSERT IGNORE instead of REPLACE INTO

Rolando A. Edwards
MySQL DBA (SCMDBA)

155 Avenue of the Americas, Fifth Floor
New York, NY 10013
212-625-5307 (Work)
201-660-3221 (Cell)
AIM & Skype : RolandoLogicWorx
redwa...@logicworks.net
http://www.linkedin.com/in/rolandoedwards


-Original Message-
From: João Cândido de Souza Neto [mailto:j...@consultorweb.cnt.br] 
Sent: Monday, February 14, 2011 1:39 PM
To: mysql@lists.mysql.com
Subject: Re: Insert only if the entry doesn't exist

Instead of "insert into" you can use "replace into".

-- 
João Cândido de Souza Neto

"Andre Polykanine"  escreveu na mensagem 
news:1621362474.20110214201...@oire.org...
Hi all,
Thanks for your fast answer to my last question!
Here's one more problem I commonly deal with.
There  are  cases  when  I need to insert the row only if such a row
doesn't  exist,  otherwise  I  need either to update the row or to do
nothing, just skip the query.
The  common case is the rating increment/decrement. I have the + and -
links  which are AJAX. So I need to prevent fast multiple clicking and
over-increasing or over-decreasing the rating of the blog entry.
Here's what I'm doing, in a simplified form (assume all of the numbers
are PHP variables):
"SELECT `Id` FROM `Votes` WHERE `EntryId`='12345' AND `UserId`='789'";
then I'm checking if the user has already clicked a link:
if (mysql_num_rows()==0) {
"INSERT INTO `Votes` SET `EntryId`='12345', `UserId`='789'";
} else {
// do nothing
}

And here is the question (at last!):
How  can  I  MySql'ly  check whether a vote does exist and if it does,
then insert the row, else either do nothing or update several columns?
I  assume I can't use ON DUPLICATE KEY UPDATE because I have no unique
keys...
Hope you understood my problem).
Thanks!



-- 
With best regards from Ukraine,
Andre
Skype: Francophile
Twitter: http://twitter.com/m_elensule
Facebook: http://facebook.com/menelion



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=redwa...@logicworks.net


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=an...@oire.org


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Insert only if the entry doesn't exist

[Andre Polykanine]

Hello Rolando,

Sorry,  but  if  I  do  INSERT IGNORE INTO, then I must indicate a key
(typically a unique key or a primary key), or is it false? But I don't
know that key and no way to get it without more queries...

-- 
With best regards from Ukraine,
Andre
Skype: Francophile
My blog: http://oire.org/menelion (mostly in Russian)
Twitter: http://twitter.com/m_elensule
Facebook: http://facebook.com/menelion

 Original message 
From: Rolando Edwards 
To: João Cândido de Souza Neto
Date created: , 10:33:05 PM
Subject: Insert only if the entry doesn't exist


  Be Careful. REPLACE INTO mechanically does DELETE and INSERT under 
mysqld's hood.
If you want to do nothing if row exists already then do:

INSERT IGNORE instead of REPLACE INTO

Rolando A. Edwards
MySQL DBA (SCMDBA)

155 Avenue of the Americas, Fifth Floor
New York, NY 10013
212-625-5307 (Work)
201-660-3221 (Cell)
AIM & Skype : RolandoLogicWorx
redwa...@logicworks.net
http://www.linkedin.com/in/rolandoedwards


-Original Message-
From: João Cândido de Souza Neto [mailto:j...@consultorweb.cnt.br] 
Sent: Monday, February 14, 2011 1:39 PM
To: mysql@lists.mysql.com
Subject: Re: Insert only if the entry doesn't exist

Instead of "insert into" you can use "replace into".

-- 
João Cândido de Souza Neto

"Andre Polykanine"  escreveu na mensagem 
news:1621362474.20110214201...@oire.org...
Hi all,
Thanks for your fast answer to my last question!
Here's one more problem I commonly deal with.
There  are  cases  when  I need to insert the row only if such a row
doesn't  exist,  otherwise  I  need either to update the row or to do
nothing, just skip the query.
The  common case is the rating increment/decrement. I have the + and -
links  which are AJAX. So I need to prevent fast multiple clicking and
over-increasing or over-decreasing the rating of the blog entry.
Here's what I'm doing, in a simplified form (assume all of the numbers
are PHP variables):
"SELECT `Id` FROM `Votes` WHERE `EntryId`='12345' AND `UserId`='789'";
then I'm checking if the user has already clicked a link:
if (mysql_num_rows()==0) {
"INSERT INTO `Votes` SET `EntryId`='12345', `UserId`='789'";
} else {
// do nothing
}

And here is the question (at last!):
How  can  I  MySql'ly  check whether a vote does exist and if it does,
then insert the row, else either do nothing or update several columns?
I  assume I can't use ON DUPLICATE KEY UPDATE because I have no unique
keys...
Hope you understood my problem).
Thanks!



-- 
With best regards from Ukraine,
Andre
Skype: Francophile
Twitter: http://twitter.com/m_elensule
Facebook: http://facebook.com/menelion



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=redwa...@logicworks.net


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=an...@oire.org


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: Insert only if the entry doesn't exist

[Rolando Edwards]

Be Careful. REPLACE INTO mechanically does DELETE and INSERT under mysqld's 
hood.
If you want to do nothing if row exists already then do:

INSERT IGNORE instead of REPLACE INTO

Rolando A. Edwards
MySQL DBA (SCMDBA)

155 Avenue of the Americas, Fifth Floor
New York, NY 10013
212-625-5307 (Work)
201-660-3221 (Cell)
AIM & Skype : RolandoLogicWorx
redwa...@logicworks.net
http://www.linkedin.com/in/rolandoedwards


-Original Message-
From: João Cândido de Souza Neto [mailto:j...@consultorweb.cnt.br] 
Sent: Monday, February 14, 2011 1:39 PM
To: mysql@lists.mysql.com
Subject: Re: Insert only if the entry doesn't exist

Instead of "insert into" you can use "replace into".

-- 
João Cândido de Souza Neto

"Andre Polykanine"  escreveu na mensagem 
news:1621362474.20110214201...@oire.org...
Hi all,
Thanks for your fast answer to my last question!
Here's one more problem I commonly deal with.
There  are  cases  when  I need to insert the row only if such a row
doesn't  exist,  otherwise  I  need either to update the row or to do
nothing, just skip the query.
The  common case is the rating increment/decrement. I have the + and -
links  which are AJAX. So I need to prevent fast multiple clicking and
over-increasing or over-decreasing the rating of the blog entry.
Here's what I'm doing, in a simplified form (assume all of the numbers
are PHP variables):
"SELECT `Id` FROM `Votes` WHERE `EntryId`='12345' AND `UserId`='789'";
then I'm checking if the user has already clicked a link:
if (mysql_num_rows()==0) {
"INSERT INTO `Votes` SET `EntryId`='12345', `UserId`='789'";
} else {
// do nothing
}

And here is the question (at last!):
How  can  I  MySql'ly  check whether a vote does exist and if it does,
then insert the row, else either do nothing or update several columns?
I  assume I can't use ON DUPLICATE KEY UPDATE because I have no unique
keys...
Hope you understood my problem).
Thanks!



-- 
With best regards from Ukraine,
Andre
Skype: Francophile
Twitter: http://twitter.com/m_elensule
Facebook: http://facebook.com/menelion



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=redwa...@logicworks.net


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Insert only if the entry doesn't exist

[Jo�o C�ndido de Souza Neto]

Instead of "insert into" you can use "replace into".

-- 
João Cândido de Souza Neto

"Andre Polykanine"  escreveu na mensagem 
news:1621362474.20110214201...@oire.org...
Hi all,
Thanks for your fast answer to my last question!
Here's one more problem I commonly deal with.
There  are  cases  when  I need to insert the row only if such a row
doesn't  exist,  otherwise  I  need either to update the row or to do
nothing, just skip the query.
The  common case is the rating increment/decrement. I have the + and -
links  which are AJAX. So I need to prevent fast multiple clicking and
over-increasing or over-decreasing the rating of the blog entry.
Here's what I'm doing, in a simplified form (assume all of the numbers
are PHP variables):
"SELECT `Id` FROM `Votes` WHERE `EntryId`='12345' AND `UserId`='789'";
then I'm checking if the user has already clicked a link:
if (mysql_num_rows()==0) {
"INSERT INTO `Votes` SET `EntryId`='12345', `UserId`='789'";
} else {
// do nothing
}

And here is the question (at last!):
How  can  I  MySql'ly  check whether a vote does exist and if it does,
then insert the row, else either do nothing or update several columns?
I  assume I can't use ON DUPLICATE KEY UPDATE because I have no unique
keys...
Hope you understood my problem).
Thanks!



-- 
With best regards from Ukraine,
Andre
Skype: Francophile
Twitter: http://twitter.com/m_elensule
Facebook: http://facebook.com/menelion



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Insert only if the entry doesn't exist

[Andre Polykanine]

Hi all,
Thanks for your fast answer to my last question!
Here's one more problem I commonly deal with.
There  are  cases  when  I need to insert the row only if such a row
doesn't  exist,  otherwise  I  need either to update the row or to do
nothing, just skip the query.
The  common case is the rating increment/decrement. I have the + and -
links  which are AJAX. So I need to prevent fast multiple clicking and
over-increasing or over-decreasing the rating of the blog entry.
Here's what I'm doing, in a simplified form (assume all of the numbers
are PHP variables):
"SELECT `Id` FROM `Votes` WHERE `EntryId`='12345' AND `UserId`='789'";
then I'm checking if the user has already clicked a link:
if (mysql_num_rows()==0) {
"INSERT INTO `Votes` SET `EntryId`='12345', `UserId`='789'";
} else {
// do nothing
}

And here is the question (at last!):
How  can  I  MySql'ly  check whether a vote does exist and if it does,
then insert the row, else either do nothing or update several columns?
I  assume I can't use ON DUPLICATE KEY UPDATE because I have no unique
keys...
Hope you understood my problem).
Thanks!



-- 
With best regards from Ukraine,
Andre
Skype: Francophile
Twitter: http://twitter.com/m_elensule
Facebook: http://facebook.com/menelion


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: CURRENT insert ID

[Jaime Crespo Rincón]

2011/1/21 Jerry Schwartz :
>>-Original Message-
>>From: João Cândido de Souza Neto [mailto:j...@consultorweb.cnt.br]
>>Sent: Friday, January 21, 2011 12:47 PM
>>To: mysql@lists.mysql.com
>>Subject: Re: CURRENT insert ID
>>
>>Ok, you must have your own reasons to do that.
>>
>>The fact is: You can´t set the auto_incremente value field to another field
>>in the same table and record even in a trigger.
>>
>>So, the best way is a second update.
>>
> [JS] That's what I'm trying to do now, but I'm using MS Access through an ODBC
> connection and I haven't figured out how to retrieve last_insert_id.

I will tell you a secret. But shh. Do not tell anyone:

--8<--8<--8<--8<--8<--8<--8<--8<--8<--8<--8<--
mysql> create table mytable(id int auto_increment primary key, name
varchar(255));
Query OK, 0 rows affected (0.07 sec)

mysql> insert into mytable (name) values ('test data');
Query OK, 1 row affected (0.00 sec)

mysql> select id from mytable where id is null;  -- OMG!!!
++
| id |
++
|  1 |
++
1 row in set (0.00 sec)
--8<--8<--8<--8<--8<--8<--8<--8<--8<--8<--8<--


-- 
Jaime Crespo
MySQL & Java Instructor
Software Developer
Warp Networks
<http://warp.es>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: CURRENT insert ID

[Mark Goodge]

On 24/01/2011 15:42, Jerry Schwartz wrote:

-Original Message-
From: Donovan Brooke [mailto:li...@euca.us]
Sent: Friday, January 21, 2011 7:28 PM
Cc: mysql@lists.mysql.com
Subject: Re: CURRENT insert ID

Just an idear..

Don't auto_increment the main table.. create a unique Id table,
auto_increment that, and grab that value first for use with both fields
in your main table.


[JS] I've thought of that, but it creates another problem.

Let's say I add a record to the ID table, thereby auto-incrementing its key.
Now I need to retrieve that key value. How do I do that while retaining some
semblance of data integrity? I'd have to do something like "SELECT MAX()",
which fails to retrieve "my" value if someone else has inserted a record in
the meantime.


That's what LAST_INSERT_ID() is for:

http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id

This is on a per-connection basis, so even if another connection inserts 
a line in the meantime your query will return the auto-increment value 
of the line you inserted.


Most programming languages with an interface to MySQL, either built-in 
or via a module, implement this natively. For example, in PHP:


mysql_query("insert into mytable set name = 'foo'");
$id = mysql_insert_id();

the value of $id will be the auto-increment number from the line you 
just inserted.


Mark
--
http://mark.goodge.co.uk
http://www.ratemysupermarket.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: CURRENT insert ID

[Jerry Schwartz]

>-Original Message-
>From: Donovan Brooke [mailto:li...@euca.us]
>Sent: Friday, January 21, 2011 7:28 PM
>Cc: mysql@lists.mysql.com
>Subject: Re: CURRENT insert ID
>
>Just an idear..
>
>Don't auto_increment the main table.. create a unique Id table,
>auto_increment that, and grab that value first for use with both fields
>in your main table.
>
[JS] I've thought of that, but it creates another problem.

Let's say I add a record to the ID table, thereby auto-incrementing its key. 
Now I need to retrieve that key value. How do I do that while retaining some 
semblance of data integrity? I'd have to do something like "SELECT MAX()", 
which fails to retrieve "my" value if someone else has inserted a record in 
the meantime.

I don't, from Access, have the ability to throw a lock on the table (so far as 
I know). I guess maybe I could do that with pass-through queries, but I'm not 
sure.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: je...@gii.co.jp
Web site: www.the-infoshop.com




>Donovan
>
>
>--
>D Brooke
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:http://lists.mysql.com/mysql?unsub=je...@gii.co.jp





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: CURRENT insert ID

[Jerry Schwartz]

I'll have to investigate how to do a transaction from Access. I guess 
pass-through queries might do it, but I'm not sure.

>-Original Message-
>From: Carsten Pedersen [mailto:cars...@bitbybit.dk]
>Sent: Sunday, January 23, 2011 9:36 AM
>To: Jerry Schwartz
>Cc: 'mysql.'
>Subject: Re: CURRENT insert ID
>
>Seeing from later posts that you're using InnoDB, why don't you simply
>wrap the INSERT/UPDATE into a transaction? You don't avoid the UPDATE,
>but I'm not sure I understand the need to mess w/ triggers.
>
>BEGIN
>INSERT INTO t(id) NULL
>UPDATE t SET xxx=last_insert_id()
>COMMIT
>
[JS] I'll have to investigate how to do a transaction from Access. I guess 
pass-through queries might do it, but I'm not sure.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: je...@gii.co.jp
Web site: www.the-infoshop.com



>Best,
>
>/ Carsten
>
>Den 21-01-2011 17:41, Jerry Schwartz skrev:
>> Here it is in a nutshell:
>>
>>
>>
>> I have a field that needs to be set equal to the auto-increment ID as a
>record is entered. I don't know how to do this without a subsequent UPDATE
>(which I can do with a trigger). Is there any way to avoid the cost of an
>UPDATE?
>>
>>
>>
>> Here's a more concrete description of the problem:
>>
>>
>>
>> CREATE TABLE t (
>>
>> id INT(11) AUTO-INCREMENT PRIMARY,
>>
>> xxx INT(11)
>>
>> );
>>
>>
>>
>> When a record is added to table `t`, I need to set `xxx` to the value
>generated for `id`. (`xxx` might be changed later.)
>>
>>
>>
>> Is there anything clever I can do?
>>
>>
>>
>> Regards,
>>
>>
>>
>> Jerry Schwartz
>>
>> Global Information Incorporated
>>
>> 195 Farmington Ave.
>>
>> Farmington, CT 06032
>>
>>
>>
>> 860.674.8796 / FAX: 860.674.8341
>>
>> E-mail:<mailto:je...@gii.co.jp>  je...@gii.co.jp
>>
>> Web site:<http://www.the-infoshop.com/>  www.the-infoshop.com
>>
>>
>>
>>




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: CURRENT insert ID

[Carsten Pedersen]

ehr...

Den 23-01-2011 15:36, Carsten Pedersen skrev:

Seeing from later posts that you're using InnoDB, why don't you simply
wrap the INSERT/UPDATE into a transaction? You don't avoid the UPDATE,
but I'm not sure I understand the need to mess w/ triggers.

BEGIN
INSERT INTO t(id) NULL
UPDATE t SET xxx=last_insert_id()


UPDATE t SET xxx=i WHERE i=last_insert_id()

obviously.

Sorry.

Best,

/ Carsten



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: CURRENT insert ID

[Carsten Pedersen]

Seeing from later posts that you're using InnoDB, why don't you simply 
wrap the INSERT/UPDATE into a transaction? You don't avoid the UPDATE, 
but I'm not sure I understand the need to mess w/ triggers.


BEGIN
INSERT INTO t(id) NULL
UPDATE t SET xxx=last_insert_id()
COMMIT

Best,

/ Carsten

Den 21-01-2011 17:41, Jerry Schwartz skrev:

Here it is in a nutshell:



I have a field that needs to be set equal to the auto-increment ID as a record 
is entered. I don’t know how to do this without a subsequent UPDATE (which I 
can do with a trigger). Is there any way to avoid the cost of an UPDATE?



Here’s a more concrete description of the problem:



CREATE TABLE t (

id INT(11) AUTO-INCREMENT PRIMARY,

xxx INT(11)

);



When a record is added to table `t`, I need to set `xxx` to the value generated 
for `id`. (`xxx` might be changed later.)



Is there anything clever I can do?



Regards,



Jerry Schwartz

Global Information Incorporated

195 Farmington Ave.

Farmington, CT 06032



860.674.8796 / FAX: 860.674.8341

E-mail:<mailto:je...@gii.co.jp>  je...@gii.co.jp

Web site:<http://www.the-infoshop.com/>  www.the-infoshop.com






--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: CURRENT insert ID

[Jesper Wisborg Krogh]

Hi,

On 22/01/2011, at 11:27 AM, Donovan Brooke wrote:

> Just an idear..
> 
> Don't auto_increment the main table.. create a unique Id table, 
> auto_increment that, and grab that value first for use with both fields in 
> your main table.

This can be wrapped into a trigger, so the main table functions as usual:

CREATE TABLE _sequence (
   Name varchar(20) NOT NULL PRIMARY KEY,
   Value INT UNSIGNED NOT NULL DEFAULT 0
) ENGINE=InnoDB;

CREATE TABLE dupkey (
   id INT UNSIGNED NOT NULL DEFAULT 0 PRIMARY KEY,
   DupKey INT UNSIGNED NOT NULL DEFAULT 0,
   Value VARCHAR(20) NOT NULL DEFAULT ''
) ENGINE=InnoDB;

INSERT INTO _sequence
VALUES ('dupkey', 0);

DELIMITER //
CREATE TRIGGER befins_dupkey BEFORE INSERT ON dupkey FOR EACH ROW
BEGIN
  DECLARE v_id INT UNSIGNED;
  
  UPDATE _sequence SET Value = (LAST_INSERT_ID(Value+1)) where name = 'dupkey';
  SET NEW.id := LAST_INSERT_ID(),
  NEW.DupKey := LAST_INSERT_ID();
END//
DELIMITER ;

INSERT INTO dupkey (Value)
VALUES ('test 1'), ('test 2');

SELECT * FROM dupkey;
++++
| id | DupKey | Value  |
++++
|  1 |  1 | test 1 |
|  2 |  2 | test 2 |
++++
2 rows in set (0.00 sec)

Cheers,
Jesper

> 
> Donovan
> 
> 
> -- 
> D Brooke
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql?unsub=my...@wisborg.dk
> 

Re: CURRENT insert ID

[Donovan Brooke]

Just an idear..

Don't auto_increment the main table.. create a unique Id table, 
auto_increment that, and grab that value first for use with both fields 
in your main table.


Donovan


--
D Brooke

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: CURRENT insert ID

[Jerry Schwartz]

>-Original Message-
>From: Michael Dykman [mailto:mdyk...@gmail.com]
>Sent: Friday, January 21, 2011 1:27 PM
>To: Jerry Schwartz
>Cc: MySql
>Subject: Re: CURRENT insert ID
>
>You don't need to do an update:
>
>...
>
>new.xxx = new.id
>...
>
[JS] I wish it were that easy. new.id is null until after the INSERT has 
completed:

SHOW CREATE TABLE xxx\G
*** 1. row **
   Table: xxx
Create Table: CREATE TABLE `xxx` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `vv` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

SHOW CREATE TRIGGER foo\G
*** 1. row ***
   Trigger: foo
  sql_mode: 
STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
SQL Original Statement: CREATE DEFINER=`access`@`%` TRIGGER foo BEFORE UPDATE 
ON xxx
FOR EACH ROW
SET NEW.vv = NEW.id
  character_set_client: utf8
  collation_connection: utf8_general_ci
Database Collation: utf8_general_ci

INSERT INTO xxx VALUES (NULL,NULL);
SELECT * FROM xxx;
++--+
| id | vv   |
++--+
|  1 | NULL |
++--+
1 row in set (0.00 sec)

I'm tearing my hair out.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: je...@gii.co.jp
Web site: www.the-infoshop.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: CURRENT insert ID

[Jerry Schwartz]

>-Original Message-
>From: João Cândido de Souza Neto [mailto:j...@consultorweb.cnt.br]
>Sent: Friday, January 21, 2011 12:47 PM
>To: mysql@lists.mysql.com
>Subject: Re: CURRENT insert ID
>
>Ok, you must have your own reasons to do that.
>
>The fact is: You can´t set the auto_incremente value field to another field
>in the same table and record even in a trigger.
>
>So, the best way is a second update.
>
[JS] That's what I'm trying to do now, but I'm using MS Access through an ODBC 
connection and I haven't figured out how to retrieve last_insert_id.

I should ask in the myodbc forum.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: je...@gii.co.jp
Web site: www.the-infoshop.com



>--
>João Cândido de Souza Neto
>
>"Darryle Steplight"  escreveu na mensagem
>news:AANLkTim+fjYUoU+1A5RG9eFS+NnaZXT7K+Ho-q=f-...@mail.gmail.com...
>@Joao - I'm currently building a database out right now that has this
>scenario. One field can be the primary key, that has a purpose for holding
>the record id, another field can hold the value. Let say there are two
>fields, id, s_id. Initially, you insert a record and `id` is now 100 and you
>update s_id to be 100.  But for whatever reason, later down the road you
>need s_id to be 200. You can just update the s_id field instead of deleting
>the entire record and inserting an entire new one with X amount of fields.
>Updating one field is a lot less work than deleting and inserting. I have my
>tables set up so I won't have to use the primary key for queries, I will
>only use the s_id field.
>
>2011/1/21 João Cândido de Souza Neto 
>
>> I can´t think about how useful for you would be to have two fields with
>> the
>> same value.
>>
>> --
>> João Cândido de Souza Neto
>>
>> ""Jerry Schwartz""  escreveu na mensagem
>> news:007501cbb98a$177acba0$467062e0$@co.jp...
>> Here it is in a nutshell:
>>
>>
>>
>> I have a field that needs to be set equal to the auto-increment ID as a
>> record is entered. I don't know how to do this without a subsequent UPDATE
>> (which I can do with a trigger). Is there any way to avoid the cost of an
>> UPDATE?
>>
>>
>>
>> Here's a more concrete description of the problem:
>>
>>
>>
>> CREATE TABLE t (
>>
>> id INT(11) AUTO-INCREMENT PRIMARY,
>>
>> xxx INT(11)
>>
>> );
>>
>>
>>
>> When a record is added to table `t`, I need to set `xxx` to the value
>> generated for `id`. (`xxx` might be changed later.)
>>
>>
>>
>> Is there anything clever I can do?
>>
>>
>>
>> Regards,
>>
>>
>>
>> Jerry Schwartz
>>
>> Global Information Incorporated
>>
>> 195 Farmington Ave.
>>
>> Farmington, CT 06032
>>
>>
>>
>> 860.674.8796 / FAX: 860.674.8341
>>
>> E-mail:  <mailto:je...@gii.co.jp> je...@gii.co.jp
>>
>> Web site:  <http://www.the-infoshop.com/> www.the-infoshop.com
>>
>>
>>
>>
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:http://lists.mysql.com/mysql?unsub=dstepli...@gmail.com
>>
>>
>
>
>--
>--
>"May the Source be with you."
>
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:http://lists.mysql.com/mysql?unsub=je...@gii.co.jp





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: CURRENT insert ID

[Michael Dykman]

You don't need to do an update:

...

new.xxx = new.id
...

On Fri, Jan 21, 2011 at 12:20 PM, Jerry Schwartz  wrote:
>>-Original Message-
>>From: Jerry Schwartz [mailto:je...@gii.co.jp]
>>Sent: Friday, January 21, 2011 11:56 AM
>>To: 'Michael Dykman'; 'MySql'
>>Subject: RE: CURRENT insert ID
>>
>>>-Original Message-
>>>From: Michael Dykman [mailto:mdyk...@gmail.com]
>>>Sent: Friday, January 21, 2011 11:50 AM
>>>To: MySql
>>>Subject: Re: CURRENT insert ID
>>>
>>>I think an ON INSERT TRIGGER would take care of this; can't think of
>>>any other way.  Using last_insert_id() in the argument list would
>>>likely yield you the previous value (which might not even related to
>>>your table.
>>>
> [JS] Alas, you cannot update a record in a trigger if the record is in the
> same table as the trigger.
>
>
> CREATE TRIGGER xx AFTER UPDATE ON t
> FOR EACH ROW
>  UPDATE t SET f1 = 7;
>
>
> That's illegal.
>
> Right now, I'm stumped.
>
>>>Having siad that..   odd requirement.
>>>
>>[JS] You don't know the half of it.
>>
>>Thanks.
>>
>>Regards,
>>
>>Jerry Schwartz
>>Global Information Incorporated
>>195 Farmington Ave.
>>Farmington, CT 06032
>>
>>860.674.8796 / FAX: 860.674.8341
>>E-mail: je...@gii.co.jp
>>Web site: www.the-infoshop.com
>>
>>
>>
>>>  - michael dykman
>>>
>>>ps  -- sorry for the duplicate Jerry, reply-to policy on this list is
>>>forever tripping me up.
>>>
>>>
>>>>
>>>> On Fri, Jan 21, 2011 at 11:41 AM, Jerry Schwartz  wrote:
>>>>> Here it is in a nutshell:
>>>>>
>>>>>
>>>>>
>>>>> I have a field that needs to be set equal to the auto-increment ID as a
>>>record is entered. I don't know how to do this without a subsequent UPDATE
>>>(which I can do with a trigger). Is there any way to avoid the cost of an
>>>UPDATE?
>>>>>
>>>>>
>>>>>
>>>>> Here's a more concrete description of the problem:
>>>>>
>>>>>
>>>>>
>>>>> CREATE TABLE t (
>>>>>
>>>>> id INT(11) AUTO-INCREMENT PRIMARY,
>>>>>
>>>>> xxx INT(11)
>>>>>
>>>>> );
>>>>>
>>>>>
>>>>>
>>>>> When a record is added to table `t`, I need to set `xxx` to the value
>>>generated for `id`. (`xxx` might be changed later.)
>>>>>
>>>>>
>>>>>
>>>>> Is there anything clever I can do?
>>>>>
>>>>>
>>>>>
>>>>> Regards,
>>>>>
>>>>>
>>>>>
>>>>> Jerry Schwartz
>>>>>
>>>>> Global Information Incorporated
>>>>>
>>>>> 195 Farmington Ave.
>>>>>
>>>>> Farmington, CT 06032
>>>>>
>>>>>
>>>>>
>>>>> 860.674.8796 / FAX: 860.674.8341
>>>>>
>>>>> E-mail:  <mailto:je...@gii.co.jp> je...@gii.co.jp
>>>>>
>>>>> Web site:  <http://www.the-infoshop.com/> www.the-infoshop.com
>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>> --
>>>>  - michael dykman
>>>>  - mdyk...@gmail.com
>>>>
>>>>  May the Source be with you.
>>>>
>>>
>>>
>>>
>>>--
>>> - michael dykman
>>> - mdyk...@gmail.com
>>>
>>> May the Source be with you.
>>>
>>>--
>>>MySQL General Mailing List
>>>For list archives: http://lists.mysql.com/mysql
>>>To unsubscribe:    http://lists.mysql.com/mysql?unsub=je...@gii.co.jp
>>
>>
>>
>>
>>
>>--
>>MySQL General Mailing List
>>For list archives: http://lists.mysql.com/mysql
>>To unsubscribe:    http://lists.mysql.com/mysql?unsub=je...@gii.co.jp
>
>
>
>
>



-- 
 - michael dykman
 - mdyk...@gmail.com

 May the Source be with you.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: CURRENT insert ID

[Jo�o C�ndido de Souza Neto]

Ok, you must have your own reasons to do that.

The fact is: You can´t set the auto_incremente value field to another field 
in the same table and record even in a trigger.

So, the best way is a second update.

-- 
João Cândido de Souza Neto

"Darryle Steplight"  escreveu na mensagem 
news:AANLkTim+fjYUoU+1A5RG9eFS+NnaZXT7K+Ho-q=f-...@mail.gmail.com...
@Joao - I'm currently building a database out right now that has this
scenario. One field can be the primary key, that has a purpose for holding
the record id, another field can hold the value. Let say there are two
fields, id, s_id. Initially, you insert a record and `id` is now 100 and you
update s_id to be 100.  But for whatever reason, later down the road you
need s_id to be 200. You can just update the s_id field instead of deleting
the entire record and inserting an entire new one with X amount of fields.
Updating one field is a lot less work than deleting and inserting. I have my
tables set up so I won't have to use the primary key for queries, I will
only use the s_id field.

2011/1/21 João Cândido de Souza Neto 

> I can´t think about how useful for you would be to have two fields with 
> the
> same value.
>
> --
> João Cândido de Souza Neto
>
> ""Jerry Schwartz""  escreveu na mensagem
> news:007501cbb98a$177acba0$467062e0$@co.jp...
> Here it is in a nutshell:
>
>
>
> I have a field that needs to be set equal to the auto-increment ID as a
> record is entered. I don't know how to do this without a subsequent UPDATE
> (which I can do with a trigger). Is there any way to avoid the cost of an
> UPDATE?
>
>
>
> Here's a more concrete description of the problem:
>
>
>
> CREATE TABLE t (
>
> id INT(11) AUTO-INCREMENT PRIMARY,
>
> xxx INT(11)
>
> );
>
>
>
> When a record is added to table `t`, I need to set `xxx` to the value
> generated for `id`. (`xxx` might be changed later.)
>
>
>
> Is there anything clever I can do?
>
>
>
> Regards,
>
>
>
> Jerry Schwartz
>
> Global Information Incorporated
>
> 195 Farmington Ave.
>
> Farmington, CT 06032
>
>
>
> 860.674.8796 / FAX: 860.674.8341
>
> E-mail:  <mailto:je...@gii.co.jp> je...@gii.co.jp
>
> Web site:  <http://www.the-infoshop.com/> www.the-infoshop.com
>
>
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql?unsub=dstepli...@gmail.com
>
>


-- 
--
"May the Source be with you."



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: CURRENT insert ID

[Darryle Steplight]

@Joao - I'm currently building a database out right now that has this
scenario. One field can be the primary key, that has a purpose for holding
the record id, another field can hold the value. Let say there are two
fields, id, s_id. Initially, you insert a record and `id` is now 100 and you
update s_id to be 100.  But for whatever reason, later down the road you
need s_id to be 200. You can just update the s_id field instead of deleting
the entire record and inserting an entire new one with X amount of fields.
Updating one field is a lot less work than deleting and inserting. I have my
tables set up so I won't have to use the primary key for queries, I will
only use the s_id field.

2011/1/21 João Cândido de Souza Neto 

> I can´t think about how useful for you would be to have two fields with the
> same value.
>
> --
> João Cândido de Souza Neto
>
> ""Jerry Schwartz""  escreveu na mensagem
> news:007501cbb98a$177acba0$467062e0$@co.jp...
> Here it is in a nutshell:
>
>
>
> I have a field that needs to be set equal to the auto-increment ID as a
> record is entered. I don't know how to do this without a subsequent UPDATE
> (which I can do with a trigger). Is there any way to avoid the cost of an
> UPDATE?
>
>
>
> Here's a more concrete description of the problem:
>
>
>
> CREATE TABLE t (
>
> id INT(11) AUTO-INCREMENT PRIMARY,
>
> xxx INT(11)
>
> );
>
>
>
> When a record is added to table `t`, I need to set `xxx` to the value
> generated for `id`. (`xxx` might be changed later.)
>
>
>
> Is there anything clever I can do?
>
>
>
> Regards,
>
>
>
> Jerry Schwartz
>
> Global Information Incorporated
>
> 195 Farmington Ave.
>
> Farmington, CT 06032
>
>
>
> 860.674.8796 / FAX: 860.674.8341
>
> E-mail:  <mailto:je...@gii.co.jp> je...@gii.co.jp
>
> Web site:  <http://www.the-infoshop.com/> www.the-infoshop.com
>
>
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql?unsub=dstepli...@gmail.com
>
>


-- 
--
"May the Source be with you."

Re: CURRENT insert ID

[Jo�o C�ndido de Souza Neto]

I can´t think about how useful for you would be to have two fields with the 
same value.

-- 
João Cândido de Souza Neto

""Jerry Schwartz""  escreveu na mensagem 
news:007501cbb98a$177acba0$467062e0$@co.jp...
Here it is in a nutshell:



I have a field that needs to be set equal to the auto-increment ID as a 
record is entered. I don't know how to do this without a subsequent UPDATE 
(which I can do with a trigger). Is there any way to avoid the cost of an 
UPDATE?



Here's a more concrete description of the problem:



CREATE TABLE t (

id INT(11) AUTO-INCREMENT PRIMARY,

xxx INT(11)

);



When a record is added to table `t`, I need to set `xxx` to the value 
generated for `id`. (`xxx` might be changed later.)



Is there anything clever I can do?



Regards,



Jerry Schwartz

Global Information Incorporated

195 Farmington Ave.

Farmington, CT 06032



860.674.8796 / FAX: 860.674.8341

E-mail:   je...@gii.co.jp

Web site:   www.the-infoshop.com






-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: CURRENT insert ID

[Jerry Schwartz]

I made a typo in my previous message.

>-Original Message-
>From: Jerry Schwartz [mailto:je...@gii.co.jp]
>Sent: Friday, January 21, 2011 12:20 PM
>To: 'Jerry Schwartz'; 'Michael Dykman'; 'MySql'
>Subject: RE: CURRENT insert ID
>
>>-Original Message-
>>From: Jerry Schwartz [mailto:je...@gii.co.jp]
>>Sent: Friday, January 21, 2011 11:56 AM
>>To: 'Michael Dykman'; 'MySql'
>>Subject: RE: CURRENT insert ID
>>
>>>-Original Message-
>>>From: Michael Dykman [mailto:mdyk...@gmail.com]
>>>Sent: Friday, January 21, 2011 11:50 AM
>>>To: MySql
>>>Subject: Re: CURRENT insert ID
>>>
>>>I think an ON INSERT TRIGGER would take care of this; can't think of
>>>any other way.  Using last_insert_id() in the argument list would
>>>likely yield you the previous value (which might not even related to
>>>your table.
>>>
>[JS] Alas, you cannot update a record in a trigger if the record is in the
>same table as the trigger.
>
>
>CREATE TRIGGER xx AFTER UPDATE ON t
>FOR EACH ROW
> UPDATE t SET f1 = 7;
>
>
[JS] That should have read "AFTER INSERT"; but it's still illegal.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: je...@gii.co.jp
Web site: www.the-infoshop.com








-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: CURRENT insert ID

[Jerry Schwartz]

>-Original Message-
>From: Jerry Schwartz [mailto:je...@gii.co.jp]
>Sent: Friday, January 21, 2011 11:56 AM
>To: 'Michael Dykman'; 'MySql'
>Subject: RE: CURRENT insert ID
>
>>-Original Message-
>>From: Michael Dykman [mailto:mdyk...@gmail.com]
>>Sent: Friday, January 21, 2011 11:50 AM
>>To: MySql
>>Subject: Re: CURRENT insert ID
>>
>>I think an ON INSERT TRIGGER would take care of this; can't think of
>>any other way.  Using last_insert_id() in the argument list would
>>likely yield you the previous value (which might not even related to
>>your table.
>>
[JS] Alas, you cannot update a record in a trigger if the record is in the 
same table as the trigger.


CREATE TRIGGER xx AFTER UPDATE ON t
FOR EACH ROW
 UPDATE t SET f1 = 7;


That's illegal.

Right now, I'm stumped.

>>Having siad that..   odd requirement.
>>
>[JS] You don't know the half of it.
>
>Thanks.
>
>Regards,
>
>Jerry Schwartz
>Global Information Incorporated
>195 Farmington Ave.
>Farmington, CT 06032
>
>860.674.8796 / FAX: 860.674.8341
>E-mail: je...@gii.co.jp
>Web site: www.the-infoshop.com
>
>
>
>>  - michael dykman
>>
>>ps  -- sorry for the duplicate Jerry, reply-to policy on this list is
>>forever tripping me up.
>>
>>
>>>
>>> On Fri, Jan 21, 2011 at 11:41 AM, Jerry Schwartz  wrote:
>>>> Here it is in a nutshell:
>>>>
>>>>
>>>>
>>>> I have a field that needs to be set equal to the auto-increment ID as a
>>record is entered. I don't know how to do this without a subsequent UPDATE
>>(which I can do with a trigger). Is there any way to avoid the cost of an
>>UPDATE?
>>>>
>>>>
>>>>
>>>> Here's a more concrete description of the problem:
>>>>
>>>>
>>>>
>>>> CREATE TABLE t (
>>>>
>>>> id INT(11) AUTO-INCREMENT PRIMARY,
>>>>
>>>> xxx INT(11)
>>>>
>>>> );
>>>>
>>>>
>>>>
>>>> When a record is added to table `t`, I need to set `xxx` to the value
>>generated for `id`. (`xxx` might be changed later.)
>>>>
>>>>
>>>>
>>>> Is there anything clever I can do?
>>>>
>>>>
>>>>
>>>> Regards,
>>>>
>>>>
>>>>
>>>> Jerry Schwartz
>>>>
>>>> Global Information Incorporated
>>>>
>>>> 195 Farmington Ave.
>>>>
>>>> Farmington, CT 06032
>>>>
>>>>
>>>>
>>>> 860.674.8796 / FAX: 860.674.8341
>>>>
>>>> E-mail:  <mailto:je...@gii.co.jp> je...@gii.co.jp
>>>>
>>>> Web site:  <http://www.the-infoshop.com/> www.the-infoshop.com
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>>
>>> --
>>>  - michael dykman
>>>  - mdyk...@gmail.com
>>>
>>>  May the Source be with you.
>>>
>>
>>
>>
>>--
>> - michael dykman
>> - mdyk...@gmail.com
>>
>> May the Source be with you.
>>
>>--
>>MySQL General Mailing List
>>For list archives: http://lists.mysql.com/mysql
>>To unsubscribe:http://lists.mysql.com/mysql?unsub=je...@gii.co.jp
>
>
>
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:http://lists.mysql.com/mysql?unsub=je...@gii.co.jp





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: CURRENT insert ID

[Jerry Schwartz]

>-Original Message-
>From: Michael Dykman [mailto:mdyk...@gmail.com]
>Sent: Friday, January 21, 2011 11:50 AM
>To: MySql
>Subject: Re: CURRENT insert ID
>
>I think an ON INSERT TRIGGER would take care of this; can't think of
>any other way.  Using last_insert_id() in the argument list would
>likely yield you the previous value (which might not even related to
>your table.
>
>Having siad that..   odd requirement.
>
[JS] You don't know the half of it.

Thanks.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: je...@gii.co.jp
Web site: www.the-infoshop.com



>  - michael dykman
>
>ps  -- sorry for the duplicate Jerry, reply-to policy on this list is
>forever tripping me up.
>
>
>>
>> On Fri, Jan 21, 2011 at 11:41 AM, Jerry Schwartz  wrote:
>>> Here it is in a nutshell:
>>>
>>>
>>>
>>> I have a field that needs to be set equal to the auto-increment ID as a
>record is entered. I don't know how to do this without a subsequent UPDATE
>(which I can do with a trigger). Is there any way to avoid the cost of an
>UPDATE?
>>>
>>>
>>>
>>> Here's a more concrete description of the problem:
>>>
>>>
>>>
>>> CREATE TABLE t (
>>>
>>> id INT(11) AUTO-INCREMENT PRIMARY,
>>>
>>> xxx INT(11)
>>>
>>> );
>>>
>>>
>>>
>>> When a record is added to table `t`, I need to set `xxx` to the value
>generated for `id`. (`xxx` might be changed later.)
>>>
>>>
>>>
>>> Is there anything clever I can do?
>>>
>>>
>>>
>>> Regards,
>>>
>>>
>>>
>>> Jerry Schwartz
>>>
>>> Global Information Incorporated
>>>
>>> 195 Farmington Ave.
>>>
>>> Farmington, CT 06032
>>>
>>>
>>>
>>> 860.674.8796 / FAX: 860.674.8341
>>>
>>> E-mail:  <mailto:je...@gii.co.jp> je...@gii.co.jp
>>>
>>> Web site:  <http://www.the-infoshop.com/> www.the-infoshop.com
>>>
>>>
>>>
>>>
>>
>>
>>
>> --
>>  - michael dykman
>>  - mdyk...@gmail.com
>>
>>  May the Source be with you.
>>
>
>
>
>--
> - michael dykman
> - mdyk...@gmail.com
>
> May the Source be with you.
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:http://lists.mysql.com/mysql?unsub=je...@gii.co.jp





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: CURRENT insert ID

[Michael Dykman]

I think an ON INSERT TRIGGER would take care of this; can't think of
any other way.  Using last_insert_id() in the argument list would
likely yield you the previous value (which might not even related to
your table.

Having siad that..   odd requirement.

  - michael dykman

ps  -- sorry for the duplicate Jerry, reply-to policy on this list is
forever tripping me up.


>
> On Fri, Jan 21, 2011 at 11:41 AM, Jerry Schwartz  wrote:
>> Here it is in a nutshell:
>>
>>
>>
>> I have a field that needs to be set equal to the auto-increment ID as a 
>> record is entered. I don’t know how to do this without a subsequent UPDATE 
>> (which I can do with a trigger). Is there any way to avoid the cost of an 
>> UPDATE?
>>
>>
>>
>> Here’s a more concrete description of the problem:
>>
>>
>>
>> CREATE TABLE t (
>>
>> id INT(11) AUTO-INCREMENT PRIMARY,
>>
>> xxx INT(11)
>>
>> );
>>
>>
>>
>> When a record is added to table `t`, I need to set `xxx` to the value 
>> generated for `id`. (`xxx` might be changed later.)
>>
>>
>>
>> Is there anything clever I can do?
>>
>>
>>
>> Regards,
>>
>>
>>
>> Jerry Schwartz
>>
>> Global Information Incorporated
>>
>> 195 Farmington Ave.
>>
>> Farmington, CT 06032
>>
>>
>>
>> 860.674.8796 / FAX: 860.674.8341
>>
>> E-mail:  <mailto:je...@gii.co.jp> je...@gii.co.jp
>>
>> Web site:  <http://www.the-infoshop.com/> www.the-infoshop.com
>>
>>
>>
>>
>
>
>
> --
>  - michael dykman
>  - mdyk...@gmail.com
>
>  May the Source be with you.
>



-- 
 - michael dykman
 - mdyk...@gmail.com

 May the Source be with you.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

CURRENT insert ID

[Jerry Schwartz]

Here it is in a nutshell:

 

I have a field that needs to be set equal to the auto-increment ID as a record 
is entered. I don’t know how to do this without a subsequent UPDATE (which I 
can do with a trigger). Is there any way to avoid the cost of an UPDATE?

 

Here’s a more concrete description of the problem:

 

CREATE TABLE t (

id INT(11) AUTO-INCREMENT PRIMARY,

xxx INT(11)

);

 

When a record is added to table `t`, I need to set `xxx` to the value generated 
for `id`. (`xxx` might be changed later.)

 

Is there anything clever I can do?

 

Regards,

 

Jerry Schwartz

Global Information Incorporated

195 Farmington Ave.

Farmington, CT 06032

 

860.674.8796 / FAX: 860.674.8341

E-mail:   je...@gii.co.jp 

Web site:   www.the-infoshop.com

 

Re: INSERT DELAYED and logging

[Alejandro Bednarik]

杨涛涛
*Stop top posting.*
*
*
*thanks.*
*
*
On Thu, Dec 23, 2010 at 12:16 AM, 杨涛涛  wrote:

> Hi.
>  I think if there are not some concurrency visitors, you should not use it.
> Otherwise, just put it.
> David Yeung, In China, Beijing.
> My First Blog:http://yueliangdao0608.cublog.cn
> My Second Blog:http://yueliangdao0608.blog.51cto.com
> My Msn: yueliangdao0...@gmail.com
>
>
>
> 2010/12/1 Wagner Bianchi 
>
> > I'll provide it to, bear with me, pls...
> >
> > Best regards.
> > --
> > WB
> >
> >
> > 2010/11/30 Johan De Meersman 
> >
> > > Interesting, but I feel the difference is rather small - could you
> rerun
> > > with, say, 50.000 queries ? Also, different concurrency levels (1, 100)
> > > might be interesting to see.
> > >
> > > Yes, I'm to lazy to do it myself, what did you think :-p
> > >
> > >
> > > On Tue, Nov 30, 2010 at 4:01 PM, Wagner Bianchi <
> > wagnerbianch...@gmail.com
> > > > wrote:
> > >
> > >> Friends, I did a benchmark regarding to this subject.
> > >> Please, I am considering your comments.
> > >> => http://wbianchi.wordpress.com/2010/11/30/insert-x-insert-delayed/
> > >>
> > >> Best regards.
> > >> --
> > >> WB
> > >>
> > >>
> > >> 2010/11/30 Wagner Bianchi 
> > >>
> > >> Maybe, the table in use must be a table that is inside cache now -
> SHOW
> > >>> OPEN TABLES, controlled by table_cache, I mean.
> > >>>
> > >>> Well, if the amount of data trasactioned is too small as a simple
> > INSERT,
> > >>> you don't have to be worried, I suggest. If you partition the table,
> we
> > must
> > >>> a benchmark to know the performance relation of a INSERT and compress
> > data
> > >>> into Archive Storage Engine or the insertion data into a partitioned
> > table.
> > >>>
> > >>> Best regards.
> > >>> --
> > >>> WB
> > >>>
> > >>>
> > >>> 2010/11/30 Johan De Meersman 
> > >>>
> > >>> I would assume that it's slower because it gets put on the delay
> thread
> > >>>> anyway, and thus executes only whenever that thread gets some
> > attention. I'm
> > >>>> not sure wether there are other influencing factors.
> > >>>>
> > >>>> I should also think that "not in use" in this context means "not
> > locked
> > >>>> against inserts", so the MyISAM insert-while-selecting at the end of
> a
> > >>>> continguous table may well apply.
> > >>>>
> > >>>> No guarantees, though - I'm not that hot on this depth.
> > >>>>
> > >>>>
> > >>>>
> > >>>> On Tue, Nov 30, 2010 at 8:46 AM, WLGades  wrote:
> > >>>>
> > >>>>> What I'm confused by though, is this line.
> > >>>>>
> > >>>>> "Note that INSERT DELAYED is slower than a normal INSERT if the
> table
> > >>>>> is not
> > >>>>> otherwise in use."  What's the definition of "in use"?  Does a
> > logging
> > >>>>> table
> > >>>>> do that given that it's pretty much append-only/write-only?
> > >>>>>
> > >>>>> Waynn
> > >>>>>
> > >>>>> On Mon, Nov 29, 2010 at 10:19 PM, Johan De Meersman <
> > >>>>> vegiv...@tuxera.be>wrote:
> > >>>>>
> > >>>>> > No, I think it's a good idea to do INSERT DELAYED here - it's
> only
> > >>>>> logging
> > >>>>> > application, and it's generally more important to not slow down
> the
> > >>>>> > application for that. It's only ever into a single table, so
> > there's
> > >>>>> only
> > >>>>> > going to be a single delay thread for it anyway.
> > >>>>> >
> > >>>>> > Archive tables are a good idea, agreed, but I suspect that
> inserts
> > >>>>> into
> > >>>>> > that are going to be slower than into regular MyISAM because of
> the
> >

Re: INSERT DELAYED and logging

[杨涛涛]

Hi.
  I think if there are not some concurrency visitors, you should not use it.
Otherwise, just put it.
David Yeung, In China, Beijing.
My First Blog:http://yueliangdao0608.cublog.cn
My Second Blog:http://yueliangdao0608.blog.51cto.com
My Msn: yueliangdao0...@gmail.com



2010/12/1 Wagner Bianchi 

> I'll provide it to, bear with me, pls...
>
> Best regards.
> --
> WB
>
>
> 2010/11/30 Johan De Meersman 
>
> > Interesting, but I feel the difference is rather small - could you rerun
> > with, say, 50.000 queries ? Also, different concurrency levels (1, 100)
> > might be interesting to see.
> >
> > Yes, I'm to lazy to do it myself, what did you think :-p
> >
> >
> > On Tue, Nov 30, 2010 at 4:01 PM, Wagner Bianchi <
> wagnerbianch...@gmail.com
> > > wrote:
> >
> >> Friends, I did a benchmark regarding to this subject.
> >> Please, I am considering your comments.
> >> => http://wbianchi.wordpress.com/2010/11/30/insert-x-insert-delayed/
> >>
> >> Best regards.
> >> --
> >> WB
> >>
> >>
> >> 2010/11/30 Wagner Bianchi 
> >>
> >> Maybe, the table in use must be a table that is inside cache now - SHOW
> >>> OPEN TABLES, controlled by table_cache, I mean.
> >>>
> >>> Well, if the amount of data trasactioned is too small as a simple
> INSERT,
> >>> you don't have to be worried, I suggest. If you partition the table, we
> must
> >>> a benchmark to know the performance relation of a INSERT and compress
> data
> >>> into Archive Storage Engine or the insertion data into a partitioned
> table.
> >>>
> >>> Best regards.
> >>> --
> >>> WB
> >>>
> >>>
> >>> 2010/11/30 Johan De Meersman 
> >>>
> >>> I would assume that it's slower because it gets put on the delay thread
> >>>> anyway, and thus executes only whenever that thread gets some
> attention. I'm
> >>>> not sure wether there are other influencing factors.
> >>>>
> >>>> I should also think that "not in use" in this context means "not
> locked
> >>>> against inserts", so the MyISAM insert-while-selecting at the end of a
> >>>> continguous table may well apply.
> >>>>
> >>>> No guarantees, though - I'm not that hot on this depth.
> >>>>
> >>>>
> >>>>
> >>>> On Tue, Nov 30, 2010 at 8:46 AM, WLGades  wrote:
> >>>>
> >>>>> What I'm confused by though, is this line.
> >>>>>
> >>>>> "Note that INSERT DELAYED is slower than a normal INSERT if the table
> >>>>> is not
> >>>>> otherwise in use."  What's the definition of "in use"?  Does a
> logging
> >>>>> table
> >>>>> do that given that it's pretty much append-only/write-only?
> >>>>>
> >>>>> Waynn
> >>>>>
> >>>>> On Mon, Nov 29, 2010 at 10:19 PM, Johan De Meersman <
> >>>>> vegiv...@tuxera.be>wrote:
> >>>>>
> >>>>> > No, I think it's a good idea to do INSERT DELAYED here - it's only
> >>>>> logging
> >>>>> > application, and it's generally more important to not slow down the
> >>>>> > application for that. It's only ever into a single table, so
> there's
> >>>>> only
> >>>>> > going to be a single delay thread for it anyway.
> >>>>> >
> >>>>> > Archive tables are a good idea, agreed, but I suspect that inserts
> >>>>> into
> >>>>> > that are going to be slower than into regular MyISAM because of the
> >>>>> > compression, so why not use that overhead to (slightly) speed up
> your
> >>>>> > end-user experience instead ?
> >>>>> >
> >>>>> > You can always partition the table based on the log date or
> whatever,
> >>>>> if
> >>>>> > your table risks getting too big.
> >>>>> >
> >>>>> >
> >>>>> >
> >>>>> > On Tue, Nov 30, 2010 at 1:03 AM, Wagner Bianchi <
> >>>>> wagnerbianch...@gmail.com
> >>>>> > > wrote:
> >>>>> >

Re: INSERT DELAYED and logging

[Wagner Bianchi]

I'll provide it to, bear with me, pls...

Best regards.
--
WB


2010/11/30 Johan De Meersman 

> Interesting, but I feel the difference is rather small - could you rerun
> with, say, 50.000 queries ? Also, different concurrency levels (1, 100)
> might be interesting to see.
>
> Yes, I'm to lazy to do it myself, what did you think :-p
>
>
> On Tue, Nov 30, 2010 at 4:01 PM, Wagner Bianchi  > wrote:
>
>> Friends, I did a benchmark regarding to this subject.
>> Please, I am considering your comments.
>> => http://wbianchi.wordpress.com/2010/11/30/insert-x-insert-delayed/
>>
>> Best regards.
>> --
>> WB
>>
>>
>> 2010/11/30 Wagner Bianchi 
>>
>> Maybe, the table in use must be a table that is inside cache now - SHOW
>>> OPEN TABLES, controlled by table_cache, I mean.
>>>
>>> Well, if the amount of data trasactioned is too small as a simple INSERT,
>>> you don't have to be worried, I suggest. If you partition the table, we must
>>> a benchmark to know the performance relation of a INSERT and compress data
>>> into Archive Storage Engine or the insertion data into a partitioned table.
>>>
>>> Best regards.
>>> --
>>> WB
>>>
>>>
>>> 2010/11/30 Johan De Meersman 
>>>
>>> I would assume that it's slower because it gets put on the delay thread
>>>> anyway, and thus executes only whenever that thread gets some attention. 
>>>> I'm
>>>> not sure wether there are other influencing factors.
>>>>
>>>> I should also think that "not in use" in this context means "not locked
>>>> against inserts", so the MyISAM insert-while-selecting at the end of a
>>>> continguous table may well apply.
>>>>
>>>> No guarantees, though - I'm not that hot on this depth.
>>>>
>>>>
>>>>
>>>> On Tue, Nov 30, 2010 at 8:46 AM, WLGades  wrote:
>>>>
>>>>> What I'm confused by though, is this line.
>>>>>
>>>>> "Note that INSERT DELAYED is slower than a normal INSERT if the table
>>>>> is not
>>>>> otherwise in use."  What's the definition of "in use"?  Does a logging
>>>>> table
>>>>> do that given that it's pretty much append-only/write-only?
>>>>>
>>>>> Waynn
>>>>>
>>>>> On Mon, Nov 29, 2010 at 10:19 PM, Johan De Meersman <
>>>>> vegiv...@tuxera.be>wrote:
>>>>>
>>>>> > No, I think it's a good idea to do INSERT DELAYED here - it's only
>>>>> logging
>>>>> > application, and it's generally more important to not slow down the
>>>>> > application for that. It's only ever into a single table, so there's
>>>>> only
>>>>> > going to be a single delay thread for it anyway.
>>>>> >
>>>>> > Archive tables are a good idea, agreed, but I suspect that inserts
>>>>> into
>>>>> > that are going to be slower than into regular MyISAM because of the
>>>>> > compression, so why not use that overhead to (slightly) speed up your
>>>>> > end-user experience instead ?
>>>>> >
>>>>> > You can always partition the table based on the log date or whatever,
>>>>> if
>>>>> > your table risks getting too big.
>>>>> >
>>>>> >
>>>>> >
>>>>> > On Tue, Nov 30, 2010 at 1:03 AM, Wagner Bianchi <
>>>>> wagnerbianch...@gmail.com
>>>>> > > wrote:
>>>>> >
>>>>> >> Well,  analyze if you need to create an excessive overhead into the
>>>>> MySQL
>>>>> >> Server because a simple INSERT. What you must have a look is it:
>>>>> >>
>>>>> >>   - How much data this connection is delivering to MySQL's handlers?
>>>>> >>   - A word DELAYED in this case is making MySQL surfer?
>>>>> >>
>>>>> >> Perhaps, you are sophisticating something that do not need it.
>>>>> Besides it,
>>>>> >> analyzing your "log table", I imagine this table can be an Archive
>>>>> table
>>>>> >> instead of MyISAM. Log tables or history tables can be controlled by
>&

Re: INSERT DELAYED and logging

[Johan De Meersman]

Interesting, but I feel the difference is rather small - could you rerun
with, say, 50.000 queries ? Also, different concurrency levels (1, 100)
might be interesting to see.

Yes, I'm to lazy to do it myself, what did you think :-p

On Tue, Nov 30, 2010 at 4:01 PM, Wagner Bianchi
wrote:

> Friends, I did a benchmark regarding to this subject.
> Please, I am considering your comments.
> => http://wbianchi.wordpress.com/2010/11/30/insert-x-insert-delayed/
>
> Best regards.
> --
> WB
>
>
> 2010/11/30 Wagner Bianchi 
>
> Maybe, the table in use must be a table that is inside cache now - SHOW
>> OPEN TABLES, controlled by table_cache, I mean.
>>
>> Well, if the amount of data trasactioned is too small as a simple INSERT,
>> you don't have to be worried, I suggest. If you partition the table, we must
>> a benchmark to know the performance relation of a INSERT and compress data
>> into Archive Storage Engine or the insertion data into a partitioned table.
>>
>> Best regards.
>> --
>> WB
>>
>>
>> 2010/11/30 Johan De Meersman 
>>
>> I would assume that it's slower because it gets put on the delay thread
>>> anyway, and thus executes only whenever that thread gets some attention. I'm
>>> not sure wether there are other influencing factors.
>>>
>>> I should also think that "not in use" in this context means "not locked
>>> against inserts", so the MyISAM insert-while-selecting at the end of a
>>> continguous table may well apply.
>>>
>>> No guarantees, though - I'm not that hot on this depth.
>>>
>>>
>>>
>>> On Tue, Nov 30, 2010 at 8:46 AM, WLGades  wrote:
>>>
>>>> What I'm confused by though, is this line.
>>>>
>>>> "Note that INSERT DELAYED is slower than a normal INSERT if the table is
>>>> not
>>>> otherwise in use."  What's the definition of "in use"?  Does a logging
>>>> table
>>>> do that given that it's pretty much append-only/write-only?
>>>>
>>>> Waynn
>>>>
>>>> On Mon, Nov 29, 2010 at 10:19 PM, Johan De Meersman >>> >wrote:
>>>>
>>>> > No, I think it's a good idea to do INSERT DELAYED here - it's only
>>>> logging
>>>> > application, and it's generally more important to not slow down the
>>>> > application for that. It's only ever into a single table, so there's
>>>> only
>>>> > going to be a single delay thread for it anyway.
>>>> >
>>>> > Archive tables are a good idea, agreed, but I suspect that inserts
>>>> into
>>>> > that are going to be slower than into regular MyISAM because of the
>>>> > compression, so why not use that overhead to (slightly) speed up your
>>>> > end-user experience instead ?
>>>> >
>>>> > You can always partition the table based on the log date or whatever,
>>>> if
>>>> > your table risks getting too big.
>>>> >
>>>> >
>>>> >
>>>> > On Tue, Nov 30, 2010 at 1:03 AM, Wagner Bianchi <
>>>> wagnerbianch...@gmail.com
>>>> > > wrote:
>>>> >
>>>> >> Well,  analyze if you need to create an excessive overhead into the
>>>> MySQL
>>>> >> Server because a simple INSERT. What you must have a look is it:
>>>> >>
>>>> >>   - How much data this connection is delivering to MySQL's handlers?
>>>> >>   - A word DELAYED in this case is making MySQL surfer?
>>>> >>
>>>> >> Perhaps, you are sophisticating something that do not need it.
>>>> Besides it,
>>>> >> analyzing your "log table", I imagine this table can be an Archive
>>>> table
>>>> >> instead of MyISAM. Log tables or history tables can be controlled by
>>>> >> Archive
>>>> >> Storage Engine to have more compressed data. Although, Archive
>>>> Storage
>>>> >> Engine only supports SELECT and INSERT. Maybe, a good deal to you,
>>>> get rid
>>>> >> of you INSERT DELAYED:
>>>> >>
>>>> >>
>>>> >>   - ALTER TABLE  ENGINE = ARCHIVE;
>>>> >>
>>>> >>
>>>> >> Best regards.
>>>> >> --
>>&

Re: INSERT DELAYED and logging

[Wagner Bianchi]

Friends, I did a benchmark regarding to this subject.
Please, I am considering your comments.
=> http://wbianchi.wordpress.com/2010/11/30/insert-x-insert-delayed/

Best regards.
--
WB


2010/11/30 Wagner Bianchi 

> Maybe, the table in use must be a table that is inside cache now - SHOW
> OPEN TABLES, controlled by table_cache, I mean.
>
> Well, if the amount of data trasactioned is too small as a simple INSERT,
> you don't have to be worried, I suggest. If you partition the table, we must
> a benchmark to know the performance relation of a INSERT and compress data
> into Archive Storage Engine or the insertion data into a partitioned table.
>
> Best regards.
> --
> WB
>
>
> 2010/11/30 Johan De Meersman 
>
> I would assume that it's slower because it gets put on the delay thread
>> anyway, and thus executes only whenever that thread gets some attention. I'm
>> not sure wether there are other influencing factors.
>>
>> I should also think that "not in use" in this context means "not locked
>> against inserts", so the MyISAM insert-while-selecting at the end of a
>> continguous table may well apply.
>>
>> No guarantees, though - I'm not that hot on this depth.
>>
>>
>>
>> On Tue, Nov 30, 2010 at 8:46 AM, WLGades  wrote:
>>
>>> What I'm confused by though, is this line.
>>>
>>> "Note that INSERT DELAYED is slower than a normal INSERT if the table is
>>> not
>>> otherwise in use."  What's the definition of "in use"?  Does a logging
>>> table
>>> do that given that it's pretty much append-only/write-only?
>>>
>>> Waynn
>>>
>>> On Mon, Nov 29, 2010 at 10:19 PM, Johan De Meersman >> >wrote:
>>>
>>> > No, I think it's a good idea to do INSERT DELAYED here - it's only
>>> logging
>>> > application, and it's generally more important to not slow down the
>>> > application for that. It's only ever into a single table, so there's
>>> only
>>> > going to be a single delay thread for it anyway.
>>> >
>>> > Archive tables are a good idea, agreed, but I suspect that inserts into
>>> > that are going to be slower than into regular MyISAM because of the
>>> > compression, so why not use that overhead to (slightly) speed up your
>>> > end-user experience instead ?
>>> >
>>> > You can always partition the table based on the log date or whatever,
>>> if
>>> > your table risks getting too big.
>>> >
>>> >
>>> >
>>> > On Tue, Nov 30, 2010 at 1:03 AM, Wagner Bianchi <
>>> wagnerbianch...@gmail.com
>>> > > wrote:
>>> >
>>> >> Well,  analyze if you need to create an excessive overhead into the
>>> MySQL
>>> >> Server because a simple INSERT. What you must have a look is it:
>>> >>
>>> >>   - How much data this connection is delivering to MySQL's handlers?
>>> >>   - A word DELAYED in this case is making MySQL surfer?
>>> >>
>>> >> Perhaps, you are sophisticating something that do not need it. Besides
>>> it,
>>> >> analyzing your "log table", I imagine this table can be an Archive
>>> table
>>> >> instead of MyISAM. Log tables or history tables can be controlled by
>>> >> Archive
>>> >> Storage Engine to have more compressed data. Although, Archive Storage
>>> >> Engine only supports SELECT and INSERT. Maybe, a good deal to you, get
>>> rid
>>> >> of you INSERT DELAYED:
>>> >>
>>> >>
>>> >>   - ALTER TABLE  ENGINE = ARCHIVE;
>>> >>
>>> >>
>>> >> Best regards.
>>> >> --
>>> >> WB
>>> >>
>>> >>
>>> >> 2010/11/29 WLGades 
>>> >>
>>> >> > I'm adding a table to our site that logs all page loads.  In the
>>> past,
>>> >> when
>>> >> > I built this, I used MyISAM and INSERT DELAYED.  I went back to look
>>> at
>>> >> the
>>> >> > documentation to see if I should still do this, and saw this (taken
>>> from
>>> >> > http://dev.mysql.com/doc/refman/5.1/en/insert-delayed.html):
>>> >> >
>>> >> > Note that INSERT DELAYED is slower than a normal INSERT if the table
>>> is
>>> >> not
>>> >> > otherwise in use. There is also the additional overhead for the
>>> server
>>> >> to
>>> >> > handle a separate thread for each table for which there are delayed
>>> >> rows.
>>> >> > This means that you should use INSERT DELAYED only when you are
>>> really
>>> >> sure
>>> >> > that you need it.
>>> >> >
>>> >> > Does that mean that I shouldn't use it if all I'm doing is INSERT
>>> >> > (essentially an append-only table), with only very occasional
>>> SELECTs?
>>> >>  In
>>> >> > addition, the last time I took this approach for logging, it worked
>>> well
>>> >> > until the table got to 65M+ rows, when it would crash every now and
>>> >> then.
>>> >> >  I
>>> >> > know I can archive off the table on a per month/quarter basis as
>>> well.
>>> >> >
>>> >> > Waynn
>>> >> >
>>> >>
>>> >
>>> >
>>> >
>>> > --
>>> > Bier met grenadyn
>>> > Is als mosterd by den wyn
>>> > Sy die't drinkt, is eene kwezel
>>> > Hy die't drinkt, is ras een ezel
>>> >
>>>
>>
>>
>>
>> --
>> Bier met grenadyn
>> Is als mosterd by den wyn
>> Sy die't drinkt, is eene kwezel
>> Hy die't drinkt, is ras een ezel
>>
>
>

Re: INSERT DELAYED and logging

[Wagner Bianchi]

Maybe, the table in use must be a table that is inside cache now - SHOW OPEN
TABLES, controlled by table_cache, I mean.

Well, if the amount of data trasactioned is too small as a simple INSERT,
you don't have to be worried, I suggest. If you partition the table, we must
a benchmark to know the performance relation of a INSERT and compress data
into Archive Storage Engine or the insertion data into a partitioned table.

Best regards.
--
WB


2010/11/30 Johan De Meersman 

> I would assume that it's slower because it gets put on the delay thread
> anyway, and thus executes only whenever that thread gets some attention. I'm
> not sure wether there are other influencing factors.
>
> I should also think that "not in use" in this context means "not locked
> against inserts", so the MyISAM insert-while-selecting at the end of a
> continguous table may well apply.
>
> No guarantees, though - I'm not that hot on this depth.
>
>
>
> On Tue, Nov 30, 2010 at 8:46 AM, WLGades  wrote:
>
>> What I'm confused by though, is this line.
>>
>> "Note that INSERT DELAYED is slower than a normal INSERT if the table is
>> not
>> otherwise in use."  What's the definition of "in use"?  Does a logging
>> table
>> do that given that it's pretty much append-only/write-only?
>>
>> Waynn
>>
>> On Mon, Nov 29, 2010 at 10:19 PM, Johan De Meersman > >wrote:
>>
>> > No, I think it's a good idea to do INSERT DELAYED here - it's only
>> logging
>> > application, and it's generally more important to not slow down the
>> > application for that. It's only ever into a single table, so there's
>> only
>> > going to be a single delay thread for it anyway.
>> >
>> > Archive tables are a good idea, agreed, but I suspect that inserts into
>> > that are going to be slower than into regular MyISAM because of the
>> > compression, so why not use that overhead to (slightly) speed up your
>> > end-user experience instead ?
>> >
>> > You can always partition the table based on the log date or whatever, if
>> > your table risks getting too big.
>> >
>> >
>> >
>> > On Tue, Nov 30, 2010 at 1:03 AM, Wagner Bianchi <
>> wagnerbianch...@gmail.com
>> > > wrote:
>> >
>> >> Well,  analyze if you need to create an excessive overhead into the
>> MySQL
>> >> Server because a simple INSERT. What you must have a look is it:
>> >>
>> >>   - How much data this connection is delivering to MySQL's handlers?
>> >>   - A word DELAYED in this case is making MySQL surfer?
>> >>
>> >> Perhaps, you are sophisticating something that do not need it. Besides
>> it,
>> >> analyzing your "log table", I imagine this table can be an Archive
>> table
>> >> instead of MyISAM. Log tables or history tables can be controlled by
>> >> Archive
>> >> Storage Engine to have more compressed data. Although, Archive Storage
>> >> Engine only supports SELECT and INSERT. Maybe, a good deal to you, get
>> rid
>> >> of you INSERT DELAYED:
>> >>
>> >>
>> >>   - ALTER TABLE  ENGINE = ARCHIVE;
>> >>
>> >>
>> >> Best regards.
>> >> --
>> >> WB
>> >>
>> >>
>> >> 2010/11/29 WLGades 
>> >>
>> >> > I'm adding a table to our site that logs all page loads.  In the
>> past,
>> >> when
>> >> > I built this, I used MyISAM and INSERT DELAYED.  I went back to look
>> at
>> >> the
>> >> > documentation to see if I should still do this, and saw this (taken
>> from
>> >> > http://dev.mysql.com/doc/refman/5.1/en/insert-delayed.html):
>> >> >
>> >> > Note that INSERT DELAYED is slower than a normal INSERT if the table
>> is
>> >> not
>> >> > otherwise in use. There is also the additional overhead for the
>> server
>> >> to
>> >> > handle a separate thread for each table for which there are delayed
>> >> rows.
>> >> > This means that you should use INSERT DELAYED only when you are
>> really
>> >> sure
>> >> > that you need it.
>> >> >
>> >> > Does that mean that I shouldn't use it if all I'm doing is INSERT
>> >> > (essentially an append-only table), with only very occasional
>> SELECTs?
>> >>  In
>> >> > addition, the last time I took this approach for logging, it worked
>> well
>> >> > until the table got to 65M+ rows, when it would crash every now and
>> >> then.
>> >> >  I
>> >> > know I can archive off the table on a per month/quarter basis as
>> well.
>> >> >
>> >> > Waynn
>> >> >
>> >>
>> >
>> >
>> >
>> > --
>> > Bier met grenadyn
>> > Is als mosterd by den wyn
>> > Sy die't drinkt, is eene kwezel
>> > Hy die't drinkt, is ras een ezel
>> >
>>
>
>
>
> --
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel
>

Re: INSERT DELAYED and logging

[Johan De Meersman]

I would assume that it's slower because it gets put on the delay thread
anyway, and thus executes only whenever that thread gets some attention. I'm
not sure wether there are other influencing factors.

I should also think that "not in use" in this context means "not locked
against inserts", so the MyISAM insert-while-selecting at the end of a
continguous table may well apply.

No guarantees, though - I'm not that hot on this depth.


On Tue, Nov 30, 2010 at 8:46 AM, WLGades  wrote:

> What I'm confused by though, is this line.
>
> "Note that INSERT DELAYED is slower than a normal INSERT if the table is
> not
> otherwise in use."  What's the definition of "in use"?  Does a logging
> table
> do that given that it's pretty much append-only/write-only?
>
> Waynn
>
> On Mon, Nov 29, 2010 at 10:19 PM, Johan De Meersman  >wrote:
>
> > No, I think it's a good idea to do INSERT DELAYED here - it's only
> logging
> > application, and it's generally more important to not slow down the
> > application for that. It's only ever into a single table, so there's only
> > going to be a single delay thread for it anyway.
> >
> > Archive tables are a good idea, agreed, but I suspect that inserts into
> > that are going to be slower than into regular MyISAM because of the
> > compression, so why not use that overhead to (slightly) speed up your
> > end-user experience instead ?
> >
> > You can always partition the table based on the log date or whatever, if
> > your table risks getting too big.
> >
> >
> >
> > On Tue, Nov 30, 2010 at 1:03 AM, Wagner Bianchi <
> wagnerbianch...@gmail.com
> > > wrote:
> >
> >> Well,  analyze if you need to create an excessive overhead into the
> MySQL
> >> Server because a simple INSERT. What you must have a look is it:
> >>
> >>   - How much data this connection is delivering to MySQL's handlers?
> >>   - A word DELAYED in this case is making MySQL surfer?
> >>
> >> Perhaps, you are sophisticating something that do not need it. Besides
> it,
> >> analyzing your "log table", I imagine this table can be an Archive table
> >> instead of MyISAM. Log tables or history tables can be controlled by
> >> Archive
> >> Storage Engine to have more compressed data. Although, Archive Storage
> >> Engine only supports SELECT and INSERT. Maybe, a good deal to you, get
> rid
> >> of you INSERT DELAYED:
> >>
> >>
> >>   - ALTER TABLE  ENGINE = ARCHIVE;
> >>
> >>
> >> Best regards.
> >> --
> >> WB
> >>
> >>
> >> 2010/11/29 WLGades 
> >>
> >> > I'm adding a table to our site that logs all page loads.  In the past,
> >> when
> >> > I built this, I used MyISAM and INSERT DELAYED.  I went back to look
> at
> >> the
> >> > documentation to see if I should still do this, and saw this (taken
> from
> >> > http://dev.mysql.com/doc/refman/5.1/en/insert-delayed.html):
> >> >
> >> > Note that INSERT DELAYED is slower than a normal INSERT if the table
> is
> >> not
> >> > otherwise in use. There is also the additional overhead for the server
> >> to
> >> > handle a separate thread for each table for which there are delayed
> >> rows.
> >> > This means that you should use INSERT DELAYED only when you are really
> >> sure
> >> > that you need it.
> >> >
> >> > Does that mean that I shouldn't use it if all I'm doing is INSERT
> >> > (essentially an append-only table), with only very occasional SELECTs?
> >>  In
> >> > addition, the last time I took this approach for logging, it worked
> well
> >> > until the table got to 65M+ rows, when it would crash every now and
> >> then.
> >> >  I
> >> > know I can archive off the table on a per month/quarter basis as well.
> >> >
> >> > Waynn
> >> >
> >>
> >
> >
> >
> > --
> > Bier met grenadyn
> > Is als mosterd by den wyn
> > Sy die't drinkt, is eene kwezel
> > Hy die't drinkt, is ras een ezel
> >
>



-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: INSERT DELAYED and logging

[WLGades]

What I'm confused by though, is this line.

"Note that INSERT DELAYED is slower than a normal INSERT if the table is not
otherwise in use."  What's the definition of "in use"?  Does a logging table
do that given that it's pretty much append-only/write-only?

Waynn

On Mon, Nov 29, 2010 at 10:19 PM, Johan De Meersman wrote:

> No, I think it's a good idea to do INSERT DELAYED here - it's only logging
> application, and it's generally more important to not slow down the
> application for that. It's only ever into a single table, so there's only
> going to be a single delay thread for it anyway.
>
> Archive tables are a good idea, agreed, but I suspect that inserts into
> that are going to be slower than into regular MyISAM because of the
> compression, so why not use that overhead to (slightly) speed up your
> end-user experience instead ?
>
> You can always partition the table based on the log date or whatever, if
> your table risks getting too big.
>
>
>
> On Tue, Nov 30, 2010 at 1:03 AM, Wagner Bianchi  > wrote:
>
>> Well,  analyze if you need to create an excessive overhead into the MySQL
>> Server because a simple INSERT. What you must have a look is it:
>>
>>   - How much data this connection is delivering to MySQL's handlers?
>>   - A word DELAYED in this case is making MySQL surfer?
>>
>> Perhaps, you are sophisticating something that do not need it. Besides it,
>> analyzing your "log table", I imagine this table can be an Archive table
>> instead of MyISAM. Log tables or history tables can be controlled by
>> Archive
>> Storage Engine to have more compressed data. Although, Archive Storage
>> Engine only supports SELECT and INSERT. Maybe, a good deal to you, get rid
>> of you INSERT DELAYED:
>>
>>
>>   - ALTER TABLE  ENGINE = ARCHIVE;
>>
>>
>> Best regards.
>> --
>> WB
>>
>>
>> 2010/11/29 WLGades 
>>
>> > I'm adding a table to our site that logs all page loads.  In the past,
>> when
>> > I built this, I used MyISAM and INSERT DELAYED.  I went back to look at
>> the
>> > documentation to see if I should still do this, and saw this (taken from
>> > http://dev.mysql.com/doc/refman/5.1/en/insert-delayed.html):
>> >
>> > Note that INSERT DELAYED is slower than a normal INSERT if the table is
>> not
>> > otherwise in use. There is also the additional overhead for the server
>> to
>> > handle a separate thread for each table for which there are delayed
>> rows.
>> > This means that you should use INSERT DELAYED only when you are really
>> sure
>> > that you need it.
>> >
>> > Does that mean that I shouldn't use it if all I'm doing is INSERT
>> > (essentially an append-only table), with only very occasional SELECTs?
>>  In
>> > addition, the last time I took this approach for logging, it worked well
>> > until the table got to 65M+ rows, when it would crash every now and
>> then.
>> >  I
>> > know I can archive off the table on a per month/quarter basis as well.
>> >
>> > Waynn
>> >
>>
>
>
>
> --
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel
>