AA V12 Build 6699_5464 MySQL Issues

[nlk10010]

I just upgraded my client's AA server to 6699_5464, redeployed their applications from the same Build of the Developer environment and started to receive MySQL connection issues; e.g. "The database cannot find the record specified". I realize this doesn't HAVE to be an issue with AA, but I scoured the Release Notes for special instructions relative to SQL operations, to no avail, and tried the applications locally on my MySQL server as well and received precisely the same errors. It's not all files, just certain ones. This has brought my client's operation to a halt and I'm seriously considering going back to a previous version of the AA server until I can figure this out.

So my question is: Has anyone experienced this kind of problem with 6699_5464, or are there Notes on this sort of thing? Just trying to see what kind of information is out there before downgrading.

Thank You all in advance.


Norman

[RustyB]

I've gotten this before when syncing, then deleting the record from the database, and then syncing again. However, I did notice around version 6311 Alpha had in the release notes that this was fixed. I haven't gotten it recently.

UX Component - List with Detail View - Fixed an issue where an error would be generated if you tried to synchronize edits to a record that had been been deleted from the database. Now, the record is removed from the List.

[nlk10010]

The actual message is "The database could not find the data you requested". At first I thought the connection might be broken, but most of the functions in the app seem to find data just fine. But your message got me thinking that it might happen when I try to execute a particular type of SQL statement. I'm going to check. If I can't figure it out I'll have to downgrade, too much of a problem right now.

Thanks for responding.

Edit: This message is thrown when I execute an SQL statement of the form "UPDATE Table SET x = a, y = b, WHERE c = z". Not sure if it's pilot error or not.

[nlk10010]

I have created a small test application: A UX with a single button that calls the following XBASIC internal function:

function Test as c (e as p)

dim connString as C = "{A5API=MySQL,Server='localhost',Port='3306',UserName='root',Password=':A5:B64:cXVpa3N0ZXA=',Database='cencarrier'}"
DIM conn as SQL::Connection
DIM flagResult as l
flagResult = conn.open(connString)
if flagResult = .f. then
Test = "alert('Error opening initial connection.');"
exit function
end if

'Specify if you are using Portable SQL syntax, or not
conn.PortableSQLEnabled = .f.
'Clear out any remnants from old invoicing. Shouldn't happen but just in case.
SQLSelectStatement = <<%sql%
UPDATE FreightBillHeaders SET Invoicing = null,InvoiceNumber = null,InvoicingOperator = null WHERE Invoicing = 1
%sql%
dim flag as l = conn.Execute(SQLSelectStatement)
if flag = .f. then
dim msg as C = "Error :" + rtrim(conn.CallResult.text) + "."
conn.close()
exit function
end if

conn.close()
Test = "Done!"
end function

When SQLSelectStatement is run, the "flag = .f." clause is executed and conn.CallResultText = "The database could not find the data you requested". Does there look to be anything untoward that would cause this? If not, then how could I submit a bug report for something that by its very nature requires a MYSQL database?

Any advice appreciated.


Norman

[RustyB]

The only difference I see from what I would have done would have been to store my connection string in the AlphaDAO connection strings and then reference it similar to the follow

DIM connStr AS C = "::Name::AADemo-Northwind"

Might not make a difference. But besides that I don't see anything that stands out to me as wrong. You have created a pretty simple test case so Alpha should be able to reproduce. Just send them the entire ux, and send them the schema to create the database table on their end in mySQL.

[nlk10010]

Thanks for the feedback.

Yes, this is driving me crazy. I've got an idea to make things even simpler, then I'll zip something up and send it in as a "bug" report. Given my experience, I'll never say it can't be pilot error, but I don't see where. By playing around it seems the problem doesn't crop up if I put a field that ISN'T being updated in the WHERE clause. But that shouldn't matter, it should be good SQL as I tested it in MySQL Workbench and got no complaints.

In the meantime I'm going to revert my client's AA Server to the Feb version. Can't fool around with this.

[nlk10010]

OK, I believe I've found the problem, hopefully it saves someone else who runs into it from tearing their hair out: Referencing the code I posted, when the WHERE clause of SQLSelectStatement ("Invoicing = 1") returns no records conn.Execute() returns False. In the version of the AA server I was using prior to 6699_5464 it returned True. True was fine, I didn't want the code to stop executing since no records satisfying "Invoicing = 1" was not an uncommon occurrence, but upgrading caused the code to fail. My guess is Alpha will say this is not a "bug", I may disagree, but it definitely is a change in behavior.

Of course there could also be other things going on, but that is a particularly nasty little wrinkle which I'm glad I caught (thanks to Rusty's help and some intensive debugging sessions).

[madtowng]

I could swear mySQL used to simply return NULL instead of a message stating no records found.
I use sql_lookup to get a count of records that satisfy the filter (ex : answer = sql_lookup(cn,tablename, "invoicenumber = 1",coalesce(count(*),'NoData') ).

I the use the if/then statement of

 if answer <> "NoData" then
cn.execute(sqlCode)
else
' Your alternative knowing there are no records that match the filter
end if

If your filter uses user defined functions you will need to do a regular query as sql_lookup
does not works with non-standard mySQL functions.

Hope this helps.

Gregg

[nlk10010]

Thank you.

For all I know MySQL may still return NULL, I'm just using the return value of conn.Execute() to error trap. Used to return .T., now it returns .F.. Exactly which SQL statements this affects I don't know, but I've gotten some more reports that other, similar queries are behaving this way. No UDFs in the WHERE clause, BTW.

[madtowng]

I've been seeing this response using V11. The query being performed is returning no records. If you try to get a count of records that match the query, You will always get a number of 0 or something greater than 0. This should eliminate the problem.