Out of sync tables

I have a innodb with a join on two tables. The foreign key is the id
column which is set to AI. I have been having an issue of the tables being
out of sync in that the id is not the same on the two tables. I have
corrected this a couple of times in phpmyadmin by resetting the
auto_increment to the same number in both tables, but it is re-occurring.

I have tried to duplicate how this occurs, and I believe it is occurring on
a page refresh, but I am not positive.

I don't know what information / code to post that someone would be able to
make a suggestion to a solution, so if someone could direct me to what to
post I would be appreciative.


--
Gary



__________ Information from ESET Smart Security, version of virus signature database 6044 (20110415) __________

The message was checked by ESET Smart Security.

http://www.eset.com





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Out of sync tables

I presume you are inserting to both tables always at the same time
inside a transaction? We would need to see the code to see how you
are inserting them..

Might I suggest, instead of the 2 part juggling act, you drop the
auto-increment property on your second table, and just use the value
derived from the first as the joining key in the second. Then there
is only one sequence to worry about with nothing to sync against

- michael dykman

On Fri, Apr 15, 2011 at 10:33 AM, Gary wrote:
> I have a innodb with a join on two tables. =A0The foreign key is the =A0i=
d
> column which is set to AI. =A0I have been having an issue of the tables b=
eing
> out of sync in that the id is not the same on the two tables. I have
> corrected this a couple of times in phpmyadmin by resetting the
> auto_increment to the same number in both tables, but it is re-occurring.
>
> I have tried to duplicate how this occurs, and I believe it is occurring =
on
> a page refresh, but I am not positive.
>
> I don't know what information / code to post that someone would be able t=
o
> make a suggestion to a solution, so if someone could direct me to what to
> post I would be appreciative.
>
>
> --
> Gary
>
>
>
> __________ Information from ESET Smart Security, version of virus signatu=
re database 6044 (20110415) __________
>
> The message was checked by ESET Smart Security.
>
> http://www.eset.com
>
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dmdykman@gmail=
..com
>
>



--=20
=A0- michael dykman
=A0- mdykman@gmail.com

=A0May 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=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Out of sync tables

Michael, thank you for your reply

""Might I suggest, instead of the 2 part juggling act, you drop the
auto-increment property on your second table, and just use the value
derived from the first as the joining key in the second. Then there
is only one sequence to worry about with nothing to sync against
""

There is only one AI into the main page. This is the insert code, I have
probably left more in than you need to see.

What I also did was to add some duplicate columns in the two tables (email,
ip, timestamp) so in the event I need to manually to in I would be able to
decifer who goes where.

On second look, it would appear I am NOT using a join, but two inserts.... I
don't recall why I did it that way

******

if (isset($_SERVER['QUERY_STRING'])) {
$editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}
$ipi_var_Recordset1 = "-1";
if (isset($_SERVER['REMOTE_ADDR'])) {
$em_var_Recordset1 = (get_magic_quotes_gpc()) ? $_SERVER['REMOTE_ADDR'] :
addslashes($_SERVER['REMOTE_ADDR']);
}
if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form")) {
$insertSQL = sprintf("INSERT INTO attorney (email, firm_name, ip) VALUES
(%s, %s, %s)",

GetSQLValueString($_POST['email'], "text"),
GetSQLValueString($_POST['firm_name'], "text"),
GetSQLValueString($_SERVER['REMOTE_ADDR'], "text"));

mysql_select_db($database_assess, $assess_remote);
$Result1 = mysql_query($insertSQL, $assess_remote) or die(mysql_error());
}
// checks if bot

if ($_POST['address'] != '' ){


die("Changed field");

}
$firmname_var_Recordset1 = "-1";
if (isset($_POST['firm_name'])) {
$firmname_var_Recordset1 = (get_magic_quotes_gpc()) ? $_POST['firm_name']
: addslashes($_POST['firm_name']);
}
$username_var_Recordset1 = "-1";
if (isset($_post['user_name'])) {
$username_var_Recordset1 = (get_magic_quotes_gpc()) ? $_POST['user_name']
: addslashes($_POST['user_name']);
}
$pw_var_Recordset1 = "-1";
if (isset($_POST['password'])) {
$pw_var_Recordset1 = (get_magic_quotes_gpc()) ? $_POST['password'] :
addslashes($_POST['password']);
}
$em_var_Recordset1 = "-1";
if (isset($_POST['email_2'])) {
$em_var_Recordset1 = (get_magic_quotes_gpc()) ? $_POST['email_2'] :
addslashes($_POST['email_2']);
}
$ip_var_Recordset1 = "-1";
if (isset($_SERVER['REMOTE_ADDR'])) {
$em_var_Recordset1 = (get_magic_quotes_gpc()) ? $_SERVER['REMOTE_ADDR'] :
addslashes($_SERVER['REMOTE_ADDR']);
}
mysql_select_db($database_assess, $assess_remote);
$query_Recordset1 = sprintf("SELECT law_firm.firm_id FROM law_firm WHERE
law_firm.firm_name = %s AND law_firm.user_name = %s AND law_firm.password=%s
AND law_firm.ip=%s" , GetSQLValueString($firmname_var_Recordset1,
"text"),GetSQLValueString($username_var_Recordset1,
"text"),GetSQLValueString($pw_var_Recordset1,
"text"),GetSQLValueString($em_var_Recordset1,
"text"),GetSQLValueString($ip_var_Recordset1, "text"));
$Recordset1 = mysql_query($query_Recordset1, $assess_remote) or
die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
$row = mysql_fetch_assoc($Recordset1);
$_SESSION['firm_id'] = $row['firm_id'];
echo $row['firm_id'];

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form")) {
$insertSQL = sprintf("INSERT INTO law_firm (firm_name, user_name,
password, slsmn, email_2, ip) VALUES (%s, %s, %s, %s, %s, %s)" ,
GetSQLValueString($_POST['firm_name'], "text"),
GetSQLValueString($_POST['user_name'], "text"),
GetSQLValueString($_POST['password'], "text"),

GetSQLValueString($_POST['slsmn'], "text"),
GetSQLValueString($_POST['email_2'], "text"),
GetSQLValueString($_SERVER['REMOTE_ADDR'], "text"));

mysql_select_db($database_assess, $assess_remote);
$Result1 = mysql_query($insertSQL, $assess_remote) or die(mysql_error());



"Michael Dykman" wrote in message
news:BANLkTi=+fpW=-qbMb6TCfFFHR1gb32Wb8Q@mail.gmail.com...
I presume you are inserting to both tables always at the same time
inside a transaction? We would need to see the code to see how you
are inserting them..

Might I suggest, instead of the 2 part juggling act, you drop the
auto-increment property on your second table, and just use the value
derived from the first as the joining key in the second. Then there
is only one sequence to worry about with nothing to sync against

- michael dykman

On Fri, Apr 15, 2011 at 10:33 AM, Gary wrote:
> I have a innodb with a join on two tables. The foreign key is the id
> column which is set to AI. I have been having an issue of the tables being
> out of sync in that the id is not the same on the two tables. I have
> corrected this a couple of times in phpmyadmin by resetting the
> auto_increment to the same number in both tables, but it is re-occurring.
>
> I have tried to duplicate how this occurs, and I believe it is occurring
> on
> a page refresh, but I am not positive.
>
> I don't know what information / code to post that someone would be able to
> make a suggestion to a solution, so if someone could direct me to what to
> post I would be appreciative.
>
>
> --
> Gary
>
>
>
> __________ Information from ESET Smart Security, version of virus
> signature database 6044 (20110415) __________
>
> The message was checked by ESET Smart Security.
>
> http://www.eset.com
>
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=mdykman@gmail.com
>
>



--
- michael dykman
- mdykman@gmail.com

May the Source be with you.

__________ Information from ESET Smart Security, version of virus signature
database 6044 (20110415) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus signature database 6044 (20110415) __________

The message was checked by ESET Smart Security.

http://www.eset.com





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Out of sync tables

--------------enig9E3637A40D907CF313961549
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable



Am 15.04.2011 17:59, schrieb Gary:
> Michael, thank you for your reply
>=20
> ""Might I suggest, instead of the 2 part juggling act, you drop the
> auto-increment property on your second table, and just use the value
> derived from the first as the joining key in the second. Then there
> is only one sequence to worry about with nothing to sync against
> ""
>=20
> There is only one AI into the main page. This is the insert code, I ha=
ve=20
> probably left more in than you need to see.
>=20
> What I also did was to add some duplicate columns in the two tables (em=
ail,=20
> ip, timestamp) so in the event I need to manually to in I would be able=
to=20
> decifer who goes where.
>=20
> On second look, it would appear I am NOT using a join, but two inserts.=
.. I=20
> don't recall why I did it that way

this code is unreadable for me because of its coding-style and if i see
"addslashes" for database inserts i start to fear and run away

you are using two inserts so what do you do there and where can be anythi=
ng
out of sync on the database-level? where is the magic in your code withou=
t
using mysql_insert_id() or LAST_INSERT_ID() - what should this code do?

* insert in main table
* fetch mysql_insert_id() what is thread-safe
* use that value in the second table
____________

and please do not use such ugly hacks as in the begin of your code
addslashes() has no useable security for user-input

even mysql_escape_string() has not -> mysql_real_escape_string()



--------------enig9E3637A40D907CF313961549
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org/

iEYEARECAAYFAk2ob04ACgkQhmBjz394Ann8YwCfd6vMflIiqASJ9uqhZgdZ ftfa
IMUAoIPat24Q9JAhg4qtLS/Y+3oXwmCH
=kT4v
-----END PGP SIGNATURE-----

--------------enig9E3637A40D907CF313961549--

Re: Out of sync tables

The first thing I notice browsing your code is this block stuck
immediately between your 2 insert statements:

if ($_POST['address'] !=3D '' ) {


die("Changed field");

}

This guarantees that your 2 auto_increment sequences will fall out of
sync any time any client POSTs (and perhaps all gets?) to this script
without an 'address' parameter.

Again, I see no reason you could not call last_insert_id() after the
first insert and use that value explicitly in the second.

- michael dykman

On Fri, Apr 15, 2011 at 12:16 PM, Reindl Harald wr=
ote:
>
>
> Am 15.04.2011 17:59, schrieb Gary:
>> Michael, thank you for your reply
>>
>> ""Might I suggest, instead of the 2 part juggling act, you drop the
>> auto-increment property on your second table, and just use the value
>> derived from the first as the joining key in the second. =A0Then there
>> is only one sequence to worry about with nothing to sync against
>> ""
>>
>> There is only one AI into the main page. =A0This is the insert code, I h=
ave
>> probably left more in than you need to see.
>>
>> What I also did was to add some duplicate columns in the two tables (ema=
il,
>> ip, timestamp) so in the event I need to manually to in I would be able =
to
>> decifer who goes where.
>>
>> On second look, it would appear I am NOT using a join, but two inserts..=
... I
>> don't recall why I did it that way
>
> this code is unreadable for me because of its coding-style and if i see
> "addslashes" for database inserts i start to fear and run away
>
> you are using two inserts so what do you do there and where can be anythi=
ng
> out of sync on the database-level? where is the magic in your code withou=
t
> using mysql_insert_id() or LAST_INSERT_ID() - what should this code do?
>
> * insert in main table
> * fetch mysql_insert_id() what is thread-safe
> * use that value in the second table
> ____________
>
> and please do not use such ugly hacks as in the begin of your code
> addslashes() has no useable security for user-input
>
> even mysql_escape_string() has not -> mysql_real_escape_string()
>
>
>



--=20
=A0- michael dykman
=A0- mdykman@gmail.com

=A0May 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=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Out of sync tables

Michael

I'm sorry, I should have removed this code for the post, but this code is
part of a "honey pot". There is no 'address' input visible, it is used so
if a spam bot enters information into this field, it kills the form. Humans
cannot enter anything into this field.

if ($_POST['address'] != '' ) {


die("Changed field");

}

""Again, I see no reason you could not call last_insert_id() after the
first insert and use that value explicitly in the second.
""

I'm not sure I undertand this, could you explain a little further for me.

Again, thank you for your help.

Gary


"Michael Dykman" wrote in message
news:BANLkTimEyeXp3LxoZVgoZ0FEDrRf69rmpg@mail.gmail.com...
The first thing I notice browsing your code is this block stuck
immediately between your 2 insert statements:

if ($_POST['address'] != '' ) {


die("Changed field");

}

This guarantees that your 2 auto_increment sequences will fall out of
sync any time any client POSTs (and perhaps all gets?) to this script
without an 'address' parameter.

Again, I see no reason you could not call last_insert_id() after the
first insert and use that value explicitly in the second.

- michael dykman

On Fri, Apr 15, 2011 at 12:16 PM, Reindl Harald
wrote:
>
>
> Am 15.04.2011 17:59, schrieb Gary:
>> Michael, thank you for your reply
>>
>> ""Might I suggest, instead of the 2 part juggling act, you drop the
>> auto-increment property on your second table, and just use the value
>> derived from the first as the joining key in the second. Then there
>> is only one sequence to worry about with nothing to sync against
>> ""
>>
>> There is only one AI into the main page. This is the insert code, I have
>> probably left more in than you need to see.
>>
>> What I also did was to add some duplicate columns in the two tables
>> (email,
>> ip, timestamp) so in the event I need to manually to in I would be able
>> to
>> decifer who goes where.
>>
>> On second look, it would appear I am NOT using a join, but two
>> inserts.... I
>> don't recall why I did it that way
>
> this code is unreadable for me because of its coding-style and if i see
> "addslashes" for database inserts i start to fear and run away
>
> you are using two inserts so what do you do there and where can be
> anything
> out of sync on the database-level? where is the magic in your code without
> using mysql_insert_id() or LAST_INSERT_ID() - what should this code do?
>
> * insert in main table
> * fetch mysql_insert_id() what is thread-safe
> * use that value in the second table
> ____________
>
> and please do not use such ugly hacks as in the begin of your code
> addslashes() has no useable security for user-input
>
> even mysql_escape_string() has not -> mysql_real_escape_string()
>
>
>



--
- michael dykman
- mdykman@gmail.com

May the Source be with you.

__________ Information from ESET Smart Security, version of virus signature
database 6044 (20110415) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus signature database 6044 (20110415) __________

The message was checked by ESET Smart Security.

http://www.eset.com





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Out of sync tables

--------------enig3F11E5F326767932F98105FC
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable



Am 15.04.2011 18:34, schrieb Gary:
> Michael
>=20
> I'm sorry, I should have removed this code for the post, but this code =
is=20
> part of a "honey pot". There is no 'address' input visible, it is used=
so=20
> if a spam bot enters information into this field, it kills the form. H=
umans=20
> cannot enter anything into this field.

but why somewhere middle in the code instead making
clean verifications at the begin?

> ""Again, I see no reason you could not call last_insert_id() after the
> first insert and use that value explicitly in the second.
> ""
>=20
> I'm not sure I undertand this, could you explain a little further for m=
e

mysql_query("insert into table1 (field1, field2) values ('value1', 'valu=
e2')", $conn);
$id =3D mysql_insert_id($conn);
mysql_query("insert into table2 (ref_id, data) values (" . $id . ", 'you=
rdata')", $conn);
?>

as long you never use the insert-id from the AI i wonder how
there could be anything out of sync because there never can
be anything in sync



--------------enig3F11E5F326767932F98105FC
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org/

iEYEARECAAYFAk2odVQACgkQhmBjz394AnnG5ACeNmWIW+21xHlRzfHgSpPc jFNF
AYAAoIfMwdsTOM9C20+2I7NjOOv5QVZE
=i22U
-----END PGP SIGNATURE-----

--------------enig3F11E5F326767932F98105FC--

Re: Out of sync tables

I'm sorry, I am unfamliar with an asc file, so I have not opened them.



GAry



"Reindl Harald" wrote in message
news:4DA87554.8030606@thelounge.net...



__________ Information from ESET Smart Security, version of virus signature database 6044 (20110415) __________

The message was checked by ESET Smart Security.

http://www.eset.com





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Out of sync tables

--------------enigB61AD831E4118B89F4A21BA5
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable


Am 15.04.2011 18:52, schrieb Gary:
> I'm sorry, I am unfamliar with an asc file, so I have not opened them.
>=20
> GAry
>
>=20
> "Reindl Harald" wrote in message=20
> news:4DA87554.8030606@thelounge.net...=20


you should not open them
if your mail client would have gpg you would see a signed message
and if not you have a useless attachment, ignore it and try to understand=
the
message and questions i sent you

congratulations:
the second person in ten years who do not
know mail-sigantures :-)


--------------enigB61AD831E4118B89F4A21BA5
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org/

iEYEARECAAYFAk2oeQIACgkQhmBjz394Annh6QCfaQcIK4MGunOXTSvYx+W7 oACb
cv8An0pLI71sJ/IiD9IZYv+vuEvgmnCk
=gzr1
-----END PGP SIGNATURE-----

--------------enigB61AD831E4118B89F4A21BA5--

Re: Out of sync tables

----- Original Message -----
> From: "Gary"
>
> I'm not sure I undertand this, could you explain a little further for
> me.

This is what they're talking about: http://dev.mysql.com/doc/refman/5.0/en/information-functions .html#function_last-insert-id


--
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=gcdmg-mysql-2@m.gmane.org

Re: Out of sync tables

Michael and Reindl

Thank you both for you help and patience. I have inserted the $id =
mysql_insert_id($connection) as well as the code in the INSERT clause and it
seems to be working fine.

One note is it turns out I did have AI on both tables, so that may have been
adding a monkey wrench.

I also took the advice to moved the honey pot code out of the middle of that
code and moved it up.

Again, thank you.

Gary




"Reindl Harald" wrote in message
news:4DA87902.9030007@thelounge.net...



__________ Information from ESET Smart Security, version of virus signature database 6045 (20110415) __________

The message was checked by ESET Smart Security.

http://www.eset.com





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org