Jump to content

Upgrade/Queries Bugs


Midnight Modding

Recommended Posts

When testing my upgrader from the final 3.x version to the first 4.x version of my app, my queries file was set to rename table tournaments_abc to tournaments_xyz and then later drop tournaments_xyz, as my plans changed. I did the drop with the NEW table name, but it gave an error saying the previous name's file doesn't exist:

'./xxxx/ibf_tournaments_xyz.frm

As a workaround, I just got rid of the renames, since it was to be dropped later in the file, anyway.

It then said another file didn't exist, where it was a rename and then updating of columns in the table. As a workaround for this bug, I moved the column updates to before the rename and put the old table name for each of those column changes.

THEN it gave an error that ANOTHER table (the actual table, this time, not the .frm file) doesn't exist, when the ONLY place in the whole file this latest table is mentioned is a createTable!!!!

    "63": {
        "method": "createTable",
        "params": [
            {
                "name": "tournaments_teams_types",
                "columns": {
                    "ttype_id": {
                        "name": "ttype_id",
                        "type": "BIGINT",
                        "length": 20,
                        "decimals": null,
                        "values": null,
                        "allow_null": false,
                        "default": "",
                        "comment": "ID Number",
                        "unsigned": true,
                        "zerofill": false,
                        "auto_increment": true,
                        "binary": false
                    },
                    "ttype_name": {
                        "name": "ttype_name",
                        "type": "VARCHAR",
                        "length": 255,
                        "decimals": null,
                        "values": null,
                        "allow_null": false,
                        "default": "",
                        "comment": "",
                        "unsigned": false,
                        "zerofill": false,
                        "auto_increment": false,
                        "binary": false
                    },
                    "ttype_friendly_name": {
                        "name": "ttype_friendly_name",
                        "type": "VARCHAR",
                        "length": 255,
                        "decimals": null,
                        "values": null,
                        "allow_null": false,
                        "default": "",
                        "comment": "",
                        "unsigned": false,
                        "zerofill": false,
                        "auto_increment": false,
                        "binary": false
                    }
                },
                "indexes": {
                    "PRIMARY": {
                        "type": "primary",
                        "name": "PRIMARY",
                        "columns": [
                            "id"
                        ],
                        "length": [
                            null
                        ]
                    }
                }
            }
        ]
    },

Notice also the next bug. It has "id" listed as the index column name, when no such column name exists anymore. Some of my other createTables have the proper column name in their primary index, so it must be that some past version of IPB had a bug and it got fixed, I assume.

So I went through the file fixing those, but still I get this error. I don't know if the number of the queries matters, but just in case... I even edited the 200 numbers when I had rearranged the queries manually trying to fix this.

I did all changes in dev center's schema tab before these issues happened, so it's not me, it's got to be bugs in the system somewhere.

So:

  1. what cam I do to fix this? I've been trying everything, but this latets error is on a very simle createTable.
  2. I noticed also the insert into a table I did was not automatically added into the upgrader! So I have to add the insert in my upgrade.php file, then? And, by the way, yes this table the insert is meant for is the same table that it's saying doesn't exist, but I doubt that is related to the issue.
  3. when manually editing queries.json, does numbering of the queries even matter or can the numbers be anything? I have no choice but to edit that file when these issues happen.

I am in a big time rush here because this app needs to be finished and this is the last hurdle.

(btw, this file has 200 queries, in case that may be playing a part? I don't think it actually gets to the point of doing them, though, before the error.)

Link to comment
Share on other sites

39 minutes ago, Midnight Modding said:

Hmm. Could it be doing the queries out of order?

May be. I indeed have tables that are renamed first and then columns are dropped. It's the old table name that cannot be found in my case:

'./xxxx/old_table_name.frm

Means, the table is renamed, then columns should be dropped. The column cannot be dropped due to "missing" table. Well, it is renamed that's why it's missing... But the order of the queries is OK in queries.json, it is created while modifiying the tables through ACP. I have not modified it manually, so it should be right order there.

Link to comment
Share on other sites

@HeadStanddo you have a suggestion on this? I feel like you, newbie, and Brandon are my best hopes on it.

I see from calendar_event_reminders indeed it has reminder_id as the primary index column, it does not leave it as "id".

I edited the very first post, trying to get everything in that to make it easier for hopefully someone to read through it.

Link to comment
Share on other sites

The error means that the table doesn't exist on disk, likely because it got renamed. I don't know why you are experiencing this without looking through your upgrade routine code and testing your upgrade routine. Presumably you have some upgrade steps that are out of order, not executing correctly, or similar.

Link to comment
Share on other sites

2 hours ago, bfarber said:

The error means that the table doesn't exist on disk, likely because it got renamed. I don't know why you are experiencing this without looking through your upgrade routine code and testing your upgrade routine. Presumably you have some upgrade steps that are out of order, not executing correctly, or similar.

edit: oops, sorry, I misread your response.

I don't even have an upgrade.php file for this version, so I couldn't have done anything out of order, and as I explained in that post, nothing was out of order in the queries file (if it had been it would have been a bug in the system, because I didn't make the file, the dev center did) and sonya said the same bug exists, as well.

Link to comment
Share on other sites

All versions between 3.x and the current one get ran (not just the most recent version), in order. First queries.json gets ran for a version, then upgrade.php, then it moves to the next version and keeps cycling through. Finally, once all of those get ran, any finish() steps in any of your upgrade.php files get ran (in order, but these happen after all of the rest of the code runs).

I understand you've spent time trying to track the issue down. I can only say that...there's an issue, and I can't tell you what it is from here without actually seeing the code and/or testing the upgrade routine.

As an aside, when this happened in Calendar in the past, it was because a random query in the queries.json file was failing (because some versions of MySQL are picky about how dates are stored in the datetime column), so the upgrader would refresh which ultimately results in the entire queries.json file being re-executed. In other words, the issue may not be "something running out of order", but it could be a problem with a different query being ran, causing that upgrade step to restart (and when it gets to the rename table statement which it already ran successfully, then it errors out). That's the sort of thing that I really just can only guess at with the information I have available.

Link to comment
Share on other sites

11 minutes ago, bfarber said:

All versions between 3.x and the current one get ran (not just the most recent version), in order. First queries.json gets ran for a version, then upgrade.php, then it moves to the next version and keeps cycling through. Finally, once all of those get ran, any finish() steps in any of your upgrade.php files get ran (in order, but these happen after all of the rest of the code runs).

I understand you've spent time trying to track the issue down. I can only say that...there's an issue, and I can't tell you what it is from here without actually seeing the code and/or testing the upgrade routine.

As an aside, when this happened in Calendar in the past, it was because a random query in the queries.json file was failing (because some versions of MySQL are picky about how dates are stored in the datetime column), so the upgrader would refresh which ultimately results in the entire queries.json file being re-executed. In other words, the issue may not be "something running out of order", but it could be a problem with a different query being ran, causing that upgrade step to restart (and when it gets to the rename table statement which it already ran successfully, then it errors out). That's the sort of thing that I really just can only guess at with the information I have available.

ok thanks. sorry, I went through all of that posting explaining how long I tested because I totally misread your post and thought you were saying I didn't test it.

Yes, that was what I was figuring is something was having it re-execute it. Do you think it could be that it has 200 queries, though? I did everything through schema tab, so it would have to be something that could happen through that tab, since I have no upgrade.php file for that particular version. Like I said, at some point it apparently had a bug related to (not) changing the column name in the primary index.

Also am I correct that I need to do an upgrade.php file to do an insert into for the upgrade routine? Scherma tab seems to have only added it to schema.json.

This is going to suck if I have to take out groups of queries and track it down. Also one thing about it possibly being that it's re-executing, though... I had other renames of tables before it, and those didn't throw the error.

Oh and plus the latest one I mentioned... that ahs no rename and is only created and that's it. So I don't think it could be that it's re-executing the whole thing. That wouldn't explain this latest one. My guess is the index column name bug was the original problem and now there is some other problem.

Link to comment
Share on other sites

Just a reminder, too, that the latest isn't saying the .frm file doesn't exist, it's saying flat out the table doesn't exist. And that one involves no renames at all. The code I posted is the entirety of the code for that table. No renames, no alters, nothing, and it says the table doesn't exist.

So my guess is the original issues were due to the index bug and this is something different. It "is" the table that has an insert in schema.json, btw, but I don't see how that could matter here.

Link to comment
Share on other sites

Again....without seeing all of the upgrade routine code, the exact error, and testing it myself, I can only guess.

Perhaps the table did not get created correctly due to the column name defined for the index being incorrect. Later when something else tried to do something (insert into it), it correctly tells you the table doesn't exist. I can only guess.

Note that sometimes logs (especially when upgrading from older versions of the software) can be written to uploads/logs/ so you may want to see if there are any associated error logs in there that may help.

Link to comment
Share on other sites

10 hours ago, bfarber said:

Again....without seeing all of the upgrade routine code, the exact error, and testing it myself, I can only guess.

Perhaps the table did not get created correctly due to the column name defined for the index being incorrect. Later when something else tried to do something (insert into it), it correctly tells you the table doesn't exist. I can only guess.

Note that sometimes logs (especially when upgrading from older versions of the software) can be written to uploads/logs/ so you may want to see if there are any associated error logs in there that may help.

I don't understand what you mean, though, because what else is there to my upgrade routine if I don't have an upgrade.php file for this? This is the first version upgrade I am testing, so nothing before it, no upgrade.php for this version, not even an insert into, I assume, since it's only in schema.json.

Link to comment
Share on other sites

Sigh. I thought I fixed it because I found one more issue due to the index bug. But it still gives the same error. 😞

For some reason, the formatting in the queries file is different for mine than an IPS one. Anything wrong with the following?

    "81": {
        "method": "changeIndex",
        "params": [
            "parent_id",
            "parent_id",
            {
                "type": "key",
                "name": "parent_id",
                "length": {
                    "0": null,
                    "2": null,
                    "3": null
                },
                "columns": {
                    "0": "parent_id",
                    "2": "tourney_start",
                    "3": "tourney_finish"
                }
            }
        ]
    },

For IPS the formatting doesn;'t list those numbers by the columns:

    "21": {
        "method": "changeIndex",
        "params": [
            "forums_forums",
            "position",
            {
                "type": "key",
                "name": "position",
                "columns": [
                    "parent_id",
                    "position"
                ],
                "length": [
                    null,
                    null
                ]
            }
        ]
    },

 

Link to comment
Share on other sites

I fixed that and I looked through ALL 200 queries, comparing their syntax to that of IPS files and I also found yet another issue related to an index, where it was a primary index not named PRIMARY. I see nothing else I can do on my end. there's a bug somewhere else in the IPS code, in my opinion.

Also, the only error logs are related to IPS tables, saying core_banfilters and core_themes don't exist. So that's some issue with the IPS upgrade routine for the official apps, not mine, and those tables do exist now.

also, another issue I brought up a while back.... happened. someone indeed got pasta  limit of how  many could join something (in my 3.x version) by 2 happening to join at the split second it changed the openings to 0.

Link to comment
Share on other sites

3 hours ago, bfarber said:

As an aside, when this happened in Calendar in the past, it was because a random query in the queries.json file was failing (because some versions of MySQL are picky about how dates are stored in the datetime column), so the upgrader would refresh which ultimately results in the entire queries.json file being re-executed. In other words, the issue may not be "something running out of order", but it could be a problem with a different query being ran, causing that upgrade step to restart (and when it gets to the rename table statement which it already ran successfully, then it errors out). That's the sort of thing that I really just can only guess at with the information I have available.

This sounds like what happens to me. This is very large table (over 15 Mio. records) and dropping a column takes a long time. Update routine does not run on command line, so there is a time-out in browser before the query is finished. If the upgrader starts the queries from the very beginning in this case, then it is an explanation what has happened here.

Link to comment
Share on other sites

I confirmed almost everything I thought form the beginning, after a lot of testing.

  1. dev center bug where it wasn't updating the index column name in the queries files. Apparently it got fixed over time, as some of my later queries were right.
  2. indeed there is still a bug where if you add a new column or table, you cannot change it at all in the same version or the queries won't execute. You must do all column changing before renames and get the columns right the first time in the addColumn or createTable.
  3. indeed number of queries matters. I got it where it did queries 1-53 properly and then did no more. But if I take 1-53 out, it does the next 50 or so correctly... the same ones it just ignored the first time, they work if they are near the top of the queries file.
  4. I did have a syntax error, as well, as I suggested in the previous post. That fix is what got the first 40 or so to work properly and then I had to change all of those changeColumn ones due to the bug I mention in #2.

So, bottom line, #1 used to be a bug, #2 still is one, and #4 happened because of me having to manually edit the file to work around #1 and #2.

Also, again, I still don't get how there is no insert for calendars for the community calendar, other than in schema.json.

Link to comment
Share on other sites

And with that... fixed. Split it up into 3 versions with 50, 50, 35 queries and so far I have checked the db to be sure the last query in each file was properly executed, and they indeed were. So most likely the rest were done, too, or it would not have gotten to the end of the files.

I was down to 135 queries because all of the work arounds caused me to get rid of some unnecessary queries. Also, it seems like it was stopping at either 50 or 51 queries each time, so I wonder if a setting somewhere is at 50 to make it stop there.

This was one exhausting day. I had to edit a ton in the file to get it all fixed, because I was working around those several issues.

Btw, there is one other very minor issue... When upgrading, it says something like "doing database work for version %s" or something like that.... so it's not replacing the %s with my versions for some reason.

Link to comment
Share on other sites

I hope someone is taking note of bugs because I am just trying to be helpful, at this point, as I already worked around them all myself and finished my app, so it doesn't affect me. I have been nearly 100% accurate for over a decade on here when I have said there is a bug, but people still doubt me in the beginning. 😛 

I confirmed yesterday that the changeIndex bug still exists in 4.3.6. In the schema tab, drop or change a column that is part of an existing index. Look in your queries file at the changeIndex query... it will have the column name for both the table name and column name and thus throw an exception on your site.

So:

  • the above changeIndex one.
  • you can't modify a column or table which was added or renamed in the current version or it will mess up the queries file.
  • the bug where you can't put a table helper table within a tab on the front end or switching between tabs eventually moves the wrong content into a tab. (confirmed by headstand)
  • inconsistent behavior with the form helper when setting disabled to TRUE. (some default values for them carry over into $form->values and some are always null instead).

There are probably others I have found, which didn't get fixed. I did see IPS fixed one I mentioned in a ticket a while back, though, where a Node on a form needed to check Add permissions or it allowed undesired behavior.

 

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...