For MySQLDump, add a comment which outputs how many rows were dumped for each table

Question

I would like to add a comment which outputs the number of rows that were dumped for each table, for MySQLDump. For this I need to modify the file client/mysqldump.c. An example output could be like this:

-- Table structure for table `t1`

DROP TABLE IF EXISTS `t1`;

/*!40101 SET @saved_cs_client     = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*!40101 SET character_set_client = @saved_cs_client */;

--

-- Dumping data for table `t1`

LOCK TABLES `t1` WRITE;

/*!40000 ALTER TABLE `t1` DISABLE KEYS */;

INSERT INTO `t1` VALUES (5);

/*!40000 ALTER TABLE `t1` ENABLE KEYS */;

UNLOCK TABLES;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

-- Rows found for 't1':  1

I haven't used MySQL much and don't understand how should I proceed towards solving this problem. Any help would be appreciated. Thanks!

Can you explain in more detail what you need? i don't understand really what's your need! — TOC, Sep 01 '12 at 00:32
When a table is dumped in mysql, I want to output it's number of rows i.e. the number of rows that were dumped. That means, if there is a variable for each table that stores the number of rows, I just need to output that, or I need to create a variable that will keep track of the count of rows. I'm a mysql noob, so I'm not sure if this task is as trivial as it sounds. — Rahul Gulati, Sep 01 '12 at 15:24

score 2 · Accepted Answer · edited May 23 '17 at 12:23

The MYSQL C API has a function that return the number of rows returned by your request mysql_num_rows (http://dev.mysql.com/doc/refman/5.0/en/mysql-num-rows.html).

You can use it with the request:

SELECT * FROM MY_TABLE;

And you got the number of rows in your table.

Here a sample code:

MYSQL * myh
MYSQL_RES *query_result;
unsigned long table_num_rows;

/* Select all records present in the table */
if (0 != mysql_query(myh, "SELECT * FROM MY_TABLE"))
{
    fprintf(stderr, "FAIL to perform the query : 'SELECT * FROM MY_TABLE' %s\n", mysql_error(myh));

    exit (EXIT_FAILURE);
}

query_result = mysql_store_result(myh);
if (query_result)
{
    /* Retreive the number of rows returned by the query, which is the total number of rows in the table
     * in our case.
    */
    table_num_rows = mysql_num_rows(query_result);
    fprintf(stdout, "Our table contain %lu\n", table_num_rows)
}

For a complete example on how to use the MYSQL C API, you can read the answer here: Writing into mysql database from a single board computer using c

EDIT : It seems that the function dump_table is used when we want to dump any table (mysqldump.c), it's a good place to add our code to count the number of rows. Modify it like this (You need to do some tests, i have not tested the code on my machine!):

static void dump_table(char *table, char *db)
{
        char ignore_flag;
        char buf[200], table_buff[NAME_LEN+3];
        DYNAMIC_STRING query_string;
        char table_type[NAME_LEN];
        char *result_table, table_buff2[NAME_LEN*2+3], *opt_quoted_table;
        int error= 0;
        ulong         rownr, row_break, total_length, init_length;
        uint num_fields;
        MYSQL_RES     *res;
        MYSQL_RES     *res_num_row;    /* Add this */
        MYSQL_FIELD   *field;
        MYSQL_ROW     row;
        char          select_expr[QUERY_LENGTH];
        unsigned long table_num_rows;   /* Add this */
        char    table_num_rows_query[256];      /* Add this */
        DBUG_ENTER("dump_table");

        /* Add this */
        /* Build the query to get the number of rows */
        snprintf(table_num_rows_query, 256, "SELECT * FROM %s", table);

        /*
         *     Make sure you get the create table info before the following check for
         *         --no-data flag below. Otherwise, the create table info won't be printed.
         *           */
        num_fields= get_table_structure(table, db, table_type, &ignore_flag);

        /*
         *     The "table" could be a view.  If so, we don't do anything here.
         *       */
        if (strcmp(table_type, "VIEW") == 0)
                DBUG_VOID_RETURN;

        /* Check --no-data flag */
        if (opt_no_data)
        {
                verbose_msg("-- Skipping dump data for table '%s', --no-data was used\n",
                                table);
                DBUG_VOID_RETURN;
        }

        DBUG_PRINT("info",
                        ("ignore_flag: %x  num_fields: %d", (int) ignore_flag,
                         num_fields));
        /*
         *     If the table type is a merge table or any type that has to be
         *          _completely_ ignored and no data dumped
         *            */
        if (ignore_flag & IGNORE_DATA)
        {
                verbose_msg("-- Warning: Skipping data for table '%s' because " \
                                "it's of type %s\n", table, table_type);
                DBUG_VOID_RETURN;
        }
        /* Check that there are any fields in the table */
        if (num_fields == 0)
        {
                verbose_msg("-- Skipping dump data for table '%s', it has no fields\n",
                                table);
                DBUG_VOID_RETURN;
        }

        /*
         *      Check --skip-events flag: it is not enough to skip creation of events
         *           discarding SHOW CREATE EVENT statements generation. The myslq.event
         *                table data should be skipped too.
         */
         if (!opt_events && !my_strcasecmp(&my_charset_latin1, db, "mysql") &&
                        !my_strcasecmp(&my_charset_latin1, table, "event"))
        {
                verbose_msg("-- Skipping data table mysql.event, --skip-events was used\n");
                DBUG_VOID_RETURN;
        }

        result_table= quote_name(table,table_buff, 1);
        opt_quoted_table= quote_name(table, table_buff2, 0);

        if (opt_lossless_fp && get_select_expr(table, select_expr))
                exit(EX_MYSQLERR);

        verbose_msg("-- Sending SELECT query...\n");

        /* Add this */
        /* TODO : check if this is the right place to put our request */
        if (0 != mysql_query(mysql, table_num_rows_query))
        {
                    fprintf(stderr, "FAIL to perform the query : %s -  %s\n", table_num_rows_query, mysql_error(myh));

                    exit (EXIT_FAILURE);
        }
        res_num_row = mysql_store_result(mysql);
        if (res_num_row)
        {
                    /* Retreive the number of rows returned by the query, which is the total number of rows in the table
                     * in our case.
                     */
                    table_num_rows = mysql_num_rows(res_num_row);
                    fprintf(stdout, "Our table contain %lu\n", table_num_rows);
        }
        /* Freeing the result */
        mysql_free_result(res_num_row);

        init_dynamic_string_checked(&query_string, "", 1024, 1024);
        /* The rest of the function here */

Thanks! This pretty much looks the right thing to me. Where do you think in this file: http://code.google.com/p/google-mysql/source/browse/client/mysqldump.c?spec=svn57a1959d9a498928248d6cc5123c9ffc5b4efe0f&name=mysql-5.1.63/03-fixes-google-1&r=57a1959d9a498928248d6cc5123c9ffc5b4efe0f should I place the following code? — Rahul Gulati, Sep 02 '12 at 00:04
@RahulGulati : I edit my answer, don't forget to test the code! — TOC, Sep 02 '12 at 02:36

For MySQLDump, add a comment which outputs how many rows were dumped for each table

-- Table structure for table t1

-- Dumping data for table t1

1 Answers1

-- Table structure for table `t1`

-- Dumping data for table `t1`