Classifieds not posting to DB

[sonjamichelle]

I'm trying to create a classified ad from within the viewer (Latest Firestorm and latest Singularity), the process goes to the publish stage but when you click publish and close out. Nothing is saved. Nothing is added to the DB. I'm running the latest release downloaded from https://github.com/InWorldz/halcyon/releases/tag/v0.9.31.6242 though my consoles and regions report 0.9.31.6257.

[appurist]

How much are you paying for the classified ad? Your balance may not cover the ad price, and if I recall correctly, you have your own economy system, so I suspect it (or the viewer) is getting an error and not reporting it for some reason.

[sonjamichelle]

Our economy system uses the builtin currency system. We just use custom webpages to access the tables and process the transactions.

I tried paying with $50 and even with $1000. Still no update.

[appurist]

And the viewer shows your balance is greater than those values?

I'm not sure what other issues there could be that could result in these symptoms: the operation appears to succeed but nothing happens. It seems like the request is probably being made by the viewer, but something is going wrong before the event is added. But I would think you would see that in the form of a message.

But if it's something like a null reference exception though, it may just abort the request, depending on whether the exception is handled. So what I'd do is pick a mostly idle region, and try to add another classified. If that one works, it may be something specific to the region or parcel the other attempts are from. If it also fails, you'll probably have a fairly noiseless and easy to spot error in the Halcyon.log file (or one of the grid service logs like User). So try it again there and see if there's any hint of the cause in the Halcyon.log file...

[sonjamichelle]

Yes I have a hefty balance for my avatar, 34K at the moment. I'll try an empty region and check the logs.

[sonjamichelle]

Ok, I went to an empty region tried a new classified posting, failed there too. Here's the Halcyon.log:

http://pastebin.com/vUf3Gpcp

[appurist]

The log shows an SQL error fetching info from the land table. On a quick check, it looks like it's just trying to fetch the parcel in the region with the nearest landing point, but whatever it is, it's looking at the parcels and landing points.

The line in question does a query on the region database (RDB), which is the one with land and prims and primitems tables, etc. That might be the same one as your main database (e.g. the one with the users table) but in our case we have separate inworldz and inworldz_rdb databases. So the first thing to check is your storage_connection_string in the [Startup] in your halcyon.ini file, since it defines the connection string to the RDB for that region. (Do not paste it here, it has database password info in it, etc.) But check if it looks correct for you. Look at the contents, find a valid region ID and try the query below to see if you can manually issue the same query. Make sure you use the same user account as specified in the storage_connection_string value. It's possible you're using the main database user account information and that doesn't match the info for the RDB, or that you haven't assigned that database account sufficient privileges to do that query on it (although in that case it probably wouldn't be able to load prims at startup either). So assuming you can connect with that db account info, and run a similar query (see below), it's probably not the RDB itself or the account access.

So the next thing to test is whether you can you do a simple query in that region, e.g. an SQL query for all land records in that region? The log excerpt posted doesn't include the region ID anywhere that I could spot, so I don't know what the region ID is. But just something like a SELECT * FROM land WHERE where RegionUUID='29bd2049-b5b6-425a-b692-18b1e4b331c6'; (replacing the region ID with the one you were in. It's possible there's only one parcel and it doesn't have a landing point set, or something like that. It shouldn't mess up this code, but maybe there's a null coming back for UserLocationX or Y instead, or something like that, and then it's doing math on that and triggering an SQL exception.

For what it's worth, I tried a classified ad on my test server and was able to run over that code without SQL errors. The actual query looks something like this:

select  uuid, MIN(ABS(UserLocationX - ?avaXLoc)) as minXValue, 
    MIN(ABS(UserLocationY - ?avaYLoc)) as minYValue 
    from land where RegionUUID=?regionUUID 
    GROUP BY UserLocationX ORDER BY minXValue, minYValue LIMIT 1;

but with the parameters filled in. So something like this:

select  uuid, MIN(ABS(UserLocationX - 249)) as minXValue, 
    MIN(ABS(UserLocationY - 35)) as minYValue
    from inworldz_rdb.land where RegionUUID='29bd2049-b5b6-425a-b692-18b1e4b331c6' 
    GROUP BY UserLocationX ORDER BY minXValue, minYValue LIMIT 1;

(those were my values)

[appurist]

If none of that works you can try setting a landing point in the parcel you're standing in, just in case, to see if that alters the results.

[appurist]

Oh I put inworldz_rdb.land in my second query above, and your RDB probably isn't named that. If you've set the default database to the one with land and prims, just use FROM land instead.

[sonjamichelle]

Ran

select  uuid, MIN(ABS(UserLocationX - 249)) as minXValue, 
    MIN(ABS(UserLocationY - 35)) as minYValue
    from inworldz.land where RegionUUID='443859a8-d63c-4a9b-9577-a1316ed87860' 
    GROUP BY UserLocationX ORDER BY minXValue, minYValue LIMIT 1;

Is what I got in return

`mysql> select  uuid, MIN(ABS(UserLocationX - 249)) as minXValue,      MIN(ABS(UserLocationY - 35)) as minYValue     from inworldz.land where RegionUUID='443859a8-d63c-4a9b-9577-a1316ed87860'      GROUP BY UserLocationX ORDER BY minXValue, minYValue LIMIT 1;
+--------------------------------------+------------------+--------------------+
| uuid                                 | minXValue        | minYValue          |
+--------------------------------------+------------------+--------------------+
| 09f899bb-1bc3-4390-af99-756e70a7cf01 | 81.3070068359375 | 103.08999633789062 |
+--------------------------------------+------------------+--------------------+
1 row in set (0.00 sec)

mysql>
`

[appurist]

Okay, good, that means there's nothing inherently wrong (e.g. not the wrong table format, or account abilities, etc).

Did the MySQL account/connection info that you used for this query match the same information provided in storage_connection_string in the [Startup] section of your halcyon.ini file? Server, user, password?

[sonjamichelle]

Yes it did.

[appurist]

I'm now looking at the actual error. I don't understand why a manual query would succeed while the region server can't make the same call with the same connection info to the same server... Normally I'd guess a MySQL version change, but it's the same server ... so... nope.

The error was:
Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column

It's complaining because the first SELECT expression is "uuid" which isn't an aggregated value like the other two that use Min(...). In theory there could be more than one, although in this case there is a LIMIT 1 on the end of the query. Although... maybe that only limits the number of returns, the first of which would still have a non-aggregated uuid.

The problem here is that it's trying to fetch the UUID so we can't specify it, If we could say FIRST(uuid) then we'd be fine but there is no such aggregate function (as far as I know).

What version of MySQL is that connecting to?

The problem I have is that I don't know how this ever works. I think there is a simple code change we could do to avoid the issue, and that is to add the uuid to the GROUP BY so that it knows there's only one match. But that would be a code change, and besides, when you do the same query to the same server it succeeds.

I don't understand why it's working unless the server is running on MySQL 5.7 and you and I are testing against a different 5.5 server version, or something like that. This could be something that will be a bug in Halcyon if sites upgrade to a newer MySQL but if that were true you'd be getting an error when you manually run the query.

[sonjamichelle]

I'm running MySQL 5.7

[appurist]

Okay. I can see that being a change in 5.7 that has caught us off guard. But I don't understand why your manual query above didn't have exactly the same error. Same server so same version number. No error.

Are you building Halcyon from source? If I suggest a source code change, does that help?

[sonjamichelle]

Yes, I grab the source from the release page, the runprebuild.bat, load the halcyon.sln into VS2015, build and then deploy.

[appurist]

ok, it may be due to the specific region and parcel (position). First, confirm you're using the region ID of either of the two regions where it didn't work. Then, if so, note the X,Y avatar position and enter that below replacing where the 249 and 35 are.

So then if you take the region ID and coordinates you're trying to create the classified at, and include those in the query below, does it then generate an error?

select  uuid, MIN(ABS(UserLocationX - 249)) as minXValue, 
    MIN(ABS(UserLocationY - 35)) as minYValue
    from inworldz.land where RegionUUID='443859a8-d63c-4a9b-9577-a1316ed87860' 
    GROUP BY UserLocationX ORDER BY minXValue, minYValue LIMIT 1;

Or, if you try this one below, which is slightly changed, again replace the 249 and 35 with your actual coordinates, does it return more than one result?

select  MIN(ABS(UserLocationX - 249)) as minXValue, 
    MIN(ABS(UserLocationY - 35)) as minYValue
    from inworldz.land where RegionUUID='443859a8-d63c-4a9b-9577-a1316ed87860' 
    GROUP BY UserLocationX ORDER BY minXValue, minYValue;

If so, I think we have our problem. Not sure what to do about it yet.

[sonjamichelle]

The Query with actual co-ords of 171x56

mysql> select  uuid, MIN(ABS(UserLocationX - 171)) as minXValue,
    ->     MIN(ABS(UserLocationY - 56)) as minYValue
    ->     from inworldz.land where RegionUUID='443859a8-d63c-4a9b-9577-a1316ed87860'
    ->     GROUP BY UserLocationX ORDER BY minXValue, minYValue LIMIT 1;
+--------------------------------------+-----------------+-------------------+
| uuid                                 | minXValue       | minYValue         |
+--------------------------------------+-----------------+-------------------+
| 09f899bb-1bc3-4390-af99-756e70a7cf01 | 3.3070068359375 | 82.08999633789062 |
+--------------------------------------+-----------------+-------------------+
1 row in set (0.00 sec)

[appurist]

and the second one? (without uuid and LIMIT 1?)

[sonjamichelle]

mysql> select  MIN(ABS(UserLocationX - 171)) as minXValue,
    ->     MIN(ABS(UserLocationY - 56)) as minYValue
    ->     from inworldz.land where RegionUUID='443859a8-d63c-4a9b-9577-a1316ed87860'
    ->     GROUP BY UserLocationX ORDER BY minXValue, minYValue;
+-----------------+-------------------+
| minXValue       | minYValue         |
+-----------------+-------------------+
| 3.3070068359375 | 82.08999633789062 |
+-----------------+-------------------+
1 row in set (0.00 sec)

[appurist]

but strange though that the first did not generate an error. I think that means it's going to give you the same results, just without the uuid column. Which means the error is probably being detected for a theoretical problem, not one that is actually occurring.

[appurist]

ok, based on this updated info, I don't have a specific code change to make. I'm going to have to ponder this some more. (And it's 3:45am here so maybe only for 15 more minutes).

[sonjamichelle]

LOL, 2:45 for me. I should be in bed too.

[appurist]

I can't release this, but it is worth a try if you want to change the source file. In the VS project, look in the Solution Explorer (if it's not visible, View->Solution Explorer menu item). In the second last folder in the big long list is OpenSimProfile.Modules. Open that and you'll find OpenProfile.cs (the one that's generating the error). Double-click to open it in the text editor window.

Go to line 343. It begins with:

string parcelLocate = "select  uuid, MIN(ABS(UserLocationX - ?avaXLoc)) as minXValue, 

(and the rest of the line not shown here).

Change that beginning to:

string parcelLocate = "select  MIN(uuid), MIN(ABS(UserLocationX - ?avaXLoc)) as minXValue, 

(and leave the rest of the line in place.) So this is changing uuid to MIN(uuid).

I think the code is just looking for the parcel UUID, and if this query ever returned more than one they'd probably all be the same. So the MIN will do. Or MAX. Or any of them.

Recompile, etc...

[sonjamichelle]

Ok, I'll give that a try. But it will have to be tomorrow. Time for bed, it's late. ;-)

[appurist]

if this query ever returned more than one they'd probably all be the same.
That might seem strange here given it's the parcel ID in the table of parcels, but it doesn't have the UNIQUE key attribute and that might also be causing MySQL to complain here.

But I think it's complaining about a theoretical problem anyway: that there may be two parcels with the same x,y as their minimum coordinates.

That can never be true because any given 4x4 square of land is in a specific parcel, so it can't be in two parcels, so the minimum X,Y for any two parcels can't be the same. But that is our usage of this table, not a given to MySQL. We know it can never happen, but technically it could based on the database schema so to MySQL, the query is ambiguous.

[appurist]

I'm heading to bed now too. Good luck and I certainly hope that resolves the issue. I believe it will. Tomorrow I will have to consider whether that is a release-quality fix. (I hesitate, since it makes me think "WHAT THE HECK?" when I see that updated query ... just with a bit more colorful language.) ;)

[appurist]

Final comment for the evening: this was definitely a MySQL version change. It changed in 5.7.5, when one of the operating modes change defaults from disabled to enabled. It's called ONLY_FULL_GROUP_BY mode, and this very problem is described here. I'll be taking a much closer look at this tomorrow.

[sonjamichelle]

If it's breakable, I'll find it and break it. ;-P

[sonjamichelle]

I made that change in the source file. Got to prepping files to move to the server for testing. Came accross trustednetworks.txt. Got to thinking about that error where it couldn't connect. I had to check, Yup! I moved my SQL server to a new SSD server last month and forgot to whitelist the new IP range.

Added the new IP range, placed the trustednetworks.txt where they needed to be, restarted services and regions, logged on, created a classified, had a currency transaction popup. Checked my profile, there was a classified in it. Did a search and there was the classified in the right category.

I REALLY do apologize for keeping you up so late with this issue.

[appurist]

The MySQL exception is not caused by the trusted networks list. That isn't used in this case, beside it is clear that you're actually getting a reply from the MySQL server. In fact, it is a MySQL reply that is specific to 5.7.5 or later.

Also, we know the cause of the problem now. So I can predict that you are running not just MySQL 5.7 but specifically 5.7.5 or later.

Did you already copy over the updated files? If so, I'd say that change resolved it, because it is certainly not a network communications restriction, so trustednetworks.txt is not related here. But it may be that there is more than one problem though, and that you needed to deal with the MySQL issue and something else in order to get an end-to-end successful classified addition.

[sonjamichelle]

No, I never deployed that changed files. I did the trustednetworks.tx before I rolling out another version of the software. I figured try that first before an unreleased patch. Since the problem was resolved I didn't move over the files. Do you want me to do another classified posting and send you logs? See if there is still an underlying issue going in the background.

[sonjamichelle]

I did another classified on a quiet region, here's the halcyon.log:

http://pastebin.com/8adLFMw0

[sonjamichelle]

Looking at the log I see this:

_2017-02-23 17:22:03,746 [STP SmartThreadPool Thread #0] DEBUG OpenSim.Region.CoreModules.Avatar.Currency.AvatarCurrency - [CURRENCY]: Source or destination avatar(s) do not exist in transaction. This is most likely a spoofed destination._

Going through my transaction history I don't see an entry for the classified payment. Who am I paying? We have a World Bank account that acts as our currency holder. Is this the avatar that it is looking for? If so, where do I specify that parameter?

[Vinhold]

I suspect this is supposed to be handled by the currency website page. However, Sonja, that page has not yet been created in your website and is actually part of the Economy process I am currently working on.
@appurist I would like a copy of the currency PHP if you have it, to use for an example to convert to the .Net website process I am building.

[appurist]

The updated log is not having the same issue at all that the first one clearly shows.

In the second one, it looks like it's ready to do the money transfer but one of the avatars does not exist. Presumably this refers to the "banker avatar" for the grid. In InWorldz, there's a "Moneybags InWorldz" that is used for the other half of the double-entry accounting.

[appurist]

The currency account has a fixed UUID:

public static UUID CURRENCY_ACCOUNT_ID = new UUID("efbfe4e6-95c2-4af3-9d27-25a35c2fd575");

as seen in AvatarCurrency.cs. Vin, if you have Classifieds working on your grid, did you define an avatar with this ID?

[Vinhold]

Yes, I have been extensively working with Sonja in the website development and am building a starting Economy management process for my template website and Sonja is currently alpha tester. :) Her bank account is Named World Bank now and has the identified UUID. Is the process failing because of the missing currency page?

[appurist]

It has nothing to do with the website. The region is unaware one exists. It's trying to look up the avatar by UUID and not finding it. (Actually, technically it's looking up both avatars and not getting a count of 2 back. ;)

[appurist]

Vin, if you have changed the UUID of the "World Bank" account to match, it may just be a matter of Sonja retrying again. Not sure if the name or UUID is specified in the Halcyon.ini file. You might need an update there or a region restart (or both) to have it pick up the change.

[Vinhold]

I have not created a classified page for my website as it would not use it. However, I have not yet reached that page for the template website so there is a lot yet to build. I have no idea how the classifieds page is supposed to work. If posting a classified is not related to any web page support, then it should be working correctly. Her World Bank UUID must match the UUID posted in the Halcyon.ini [Economy] section for the world bank UUID.

[Vinhold]

If the World Bank UUID is hard coded as what you posted above, it should be replaced with the specified bank account UUID in the Halcyon.ini [Economy] section. That is the only info we have to set externally to the Halcyon program. :)

[appurist]

Vin, none of this relies on the website. There is no currency.php file. There is just a currency avatar to accept the money transfers. (The server needs somewhere to put the money transfer.)

It's unclear why you would need to specify a UUID of the currency avatar. Do you have working system payments (e.g. classifieds) on GW grid? (If so, what did you used for the currency account?) It could certainly be made an option, but if you just use the same UUID, it would work right now, and not need an ini option.

[appurist]

Also, I don't know what you mean by the specified bank account. There's no way to set the bank account UUID.

[Vinhold]

In the GW grid, I only use a World Bank user account, and it has its own UUID which I placed in my Halcyon.ini. So far I have no problem as it is only used for checking if the user has enough funds to pay for upload or group create. There is no entry to place a price on classifieds in the Halcyon.ini [Economy] settings. So again if there is a hard coded UUID for the CURRENCY_ACCOUNT_ID I would post a correction to have it set to the [Economy] UUID for the world bank. I have a full working economy system being built for the template website which Sonja is using once she gets this next website update. It is all built around that Halcyon.ini bank account UUID entry.

[Vinhold]

Halcyon.ini
[Economy]
; These economy values get used in the BetaGridLikeMoneyModule. - This module is for demonstration only -
; In grid mode, use this currency XMLRPC server. Leave blank for normal functionality
CurrencyServer = ""
;"http://www.GospelLearningCenter.com/GospelWorld/currency.aspx"

; This is the account Money goes to for fees.  Remember, economy requires that money circulates somewhere... even if it's an upload fee
EconomyBaseAccount = 7fa925e5-a1b6-47e3-b040-0633dff44797

That is what I am referring to. There is supposed to be a currency page and the EconomyBaseAccount identifies who the world bank is supposed to be.

[appurist]

No, as the comment says, they should not be used for normal functionality. I believe that was used in an old OpenSim module (probably called "BetaGridLikeMoneyModule") that was used for some specific non-production tests that faked an economy module to avoid the real one. In the real code (i.e. the code that was getting the MySQL error last night), this has never been defined. And I just searched for "CurrencyServer" and it doesn't appear in server code anywhere. So that was some kind of debug/test environment an early OpenSim developer used for testing.

[Vinhold]

That was the instructions I had from David when I first set up GW world, that was the way to identify the world bank account using EconomyBaseAccount and it has been running in GW ever since. I can give cash to any other account and the transaction tables all get the correct entries. That is also how Sonja's grid is working with all other transactions including land transfers for her and GW land sales.

[appurist]

Here's a discussion of it, and it looks like the module was called SampleMoneyModule externally based on this.

[appurist]

Hmm, in that case let me see what the main grid is using...

[Vinhold]

Ok. How about making it a request to update to Halcyon to use the EconomyBaseAccount to override that hard coded UUID for the world bank? It is not a good idea to have that hard coded in the system with no identity that can be accessed by any external website process that needs to reference it.
I have identified three user accounts that are minimal to create a grid: Grid Owner (identified as the Master Avatar), Grid Library (a hack until there is a process to easily maintain the nasty XML file tree for the Library) and the World Bank. I am making the Ecnomy system to actually create the World Bank avatar and get its UUID to place in the Halcyon.ini for the simulator to use.

[appurist]

As I said, we can always add support for that, I just don't see any support for that in the money-handling server code. (And there's a release window for such a change; code freeze for the next release is not until Tuesday night). I was indicating how to get it running right now.

[appurist]

I see such settings on the InWorldz main grid, but I don't see any support for this in the actual Halcyon server code. Perhaps there's an optional money module, that matches the SampleMoneyModule settings, that is added to InWorldz code. (Checking...)

[Vinhold]

Economy management was always an optional external module to OpenSim. One that most of the time you had to pay for to get. It is not built in to Halcyon and that was part of the info David told me at the start. I did not care for GW would never use an economy system. So long as upload and group create had $amounts set and checked for user balances I can manage who gets to upload and create groups in GW.

[appurist]

Okay, the InWorldz halcyon.ini file specifies a currency.php file on inworldz.com but it is not used. It does not exist on the website. And there is no reference anywhere to "EconomyBaseAccount" (or even "EconomyBase") in the server code. I think you may have been given stale information, assumed to be accurate because of the presence of the old settings in our halcyon.ini files.

[appurist]

I've created an issue specifically for the enhancement to support an economy avatar account ID override, it is #333. We'll try to get that in for the 0.9.32 release. Code freeze is 5 days from now, IW release should follow a week later (March 7) and GitHub release probably March 9 or 10. But as soon as it is committed, you could apply this, or build from the 0.9.32 branch which will appear at code freeze Tuesday night.

[appurist]

That change is committed and PR'd, so closing this one now.