So I'm wondering how I might use a query to check the autoincrement value for a table in MySQL. I had some luck using this on my dev machine but it apparently requires privileges I don't have on my production machine:

SELECT Auto_increment FROM information_schema.tables WHERE table_schema='mbamdev_newmig2' AND table_name='products';

    I have written a lengthy migration script in pure SQL. I truncate some tables and then insert a lot of data from a separate DB. I want to check the autoincrement values that result after I've inserted all this data to make sure it looks OK. I do not want to use any PHP for this command -- only pure sql.

      what do you want to check these for, I mean: what do you want to find out? Migrating sounds like you have perfect control over the DB with no other user interfering. Why can't you simply inspect the autoincrements?

        I want to know when my migration is complete that the autoincrement values are higher than the highest id that has been inserted. This appears to be the case on my local dev machine but the server has a different version of mySQL. It's just a simple sanity check.

        The means I have to access MySQL on the remote server are limited to a mysql command-line client and whatever remote desktop client I can download. I'm using SQLYog at the moment and there is no way to check the autoincrements for a given table as far as I can tell.

        aha. I just tried MySQL administrator (desktop GUI made by MySQL folks) and I was able to dig in and find the autoincrements. This software seems REALLY slow though. It takes nearly 30 seconds just to list my tables.

        I find it hard to believe there's not an SQL command to show the autoincrement value for a given table in MySQL. How would one check this with the command-line client?

          SELECT TABLE_NAME, AUTO_INCREMENT FROM TABLES

          query the information_schema database...

          The MySQL Administrator takes some time for opening the "catalogue" but the query-analyzer that comes with it works at a good speed.

            I appreciate your input on this a lot, but I'm not sure what to do with that query:

            SELECT `TABLE_NAME`, `AUTO_INCREMENT` FROM `TABLES`

            I get error:
            Error Code : 1064
            You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TABLE_NAME' at line 1

            If I have a particular database selected I get this one:
            Error Code : 1146
            Table 'mbamdev_current.TABLES' doesn't exist

              SELECT TABLE_NAME, AUTO_INCREMENT FROM `information_schema`.`TABLES`

              Every MySQL DB comes with a sister: the information_schema DB. It holds a bunch of useful info about the DB itself. In MySQL admin you can explore it easily by selecting it from the catalogue list. (or use queries like above)

                aha. I had tried that query before. I lack permissions to query that table:
                Error Code : 1142
                SELECT command denied to user 'mbamdev_dba2'@'cpe-76-175-168-110.socal.res.rr.com' for table 'TABLES'

                  Hi again,

                  Just a thought - if you're really stuck a 'messy' solution might be to do a test insert, pluck the last_insert_id() and then delete the row you've just inserted.

                  P

                    You guys are both awesome for sticking with this. I suppose using the MySQL Admin software is not so bad. It's doing the trick for now. I'm going to make this resolved.

                      Maybe you need to connect as a different user? If the user you connect with has all the rights you need for migrating the DB stuff, then it's a bit silly that he lacks reading rights on the information_schema DB. If you have GRANT permission you can set him up accordingly with one single GRANT query, if not - get the DBAs to do this.

                      Anyway: you seem to have got your info through the MySQL admin...glad I could help.

                      @: wow, yes that is messy. (but it works I guess, extra points for creativity)

                      Bjom

                      P.S.: If you feel like it and have the time: check out my code that I linked to under "Code Critique"

                        Write a Reply...