/* * changeuser.c - demonstrate non-reset of LAST_INSERT_ID after * mysql_change_user(). Program enables multi-statement execution, * but calls mysql_change_user() after each input line. * Demonstrate problem like this: % ./changeuser test query> DROP TABLE IF EXISTS t 0 rows affected query> DROP TABLE IF EXISTS t;CREATE TABLE t (i SERIAL) 0 rows affected 0 rows affected query> INSERT INTO t SET i = NULL;INSERT INTO t SET i = NULL;SELECT LAST_INSERT_ID() 1 rows affected 1 rows affected +------------------+ | LAST_INSERT_ID() | +------------------+ | 2 | +------------------+ 1 rows returned query> SELECT LAST_INSERT_ID() +------------------+ | LAST_INSERT_ID() | +------------------+ | 2 | +------------------+ 1 rows returned The result of that last statement should be 0. It can be seen that other state-related information such as user variables *are* reset as follows: query> SELECT @x;SET @x = 1; SELECT @x +------+ | @x | +------+ | NULL | +------+ 1 rows returned 0 rows affected +------+ | @x | +------+ | 1 | +------+ 1 rows returned query> SELECT @x +------+ | @x | +------+ | NULL | +------+ 1 rows returned */ #include /* for strdup() */ #include #include #include #include static char *opt_host_name = NULL; /* server host (default=localhost) */ static char *opt_user_name = NULL; /* username (default=login name) */ static char *opt_password = NULL; /* password (default=none) */ static unsigned int opt_port_num = 0; /* port number (use built-in value) */ static char *opt_socket_name = NULL; /* socket name (use built-in value) */ static char *opt_db_name = NULL; /* database name (default=none) */ static unsigned int opt_flags = 0; /* connection flags (none) */ static int ask_password = 0; /* whether to solicit password */ static MYSQL *conn; /* pointer to connection handler */ static const char *client_groups[] = { "client", NULL }; static struct my_option my_opts[] = /* option information structures */ { {"help", '?', "Display this help and exit", NULL, NULL, NULL, GET_NO_ARG, NO_ARG, 0, 0, 0, 0, 0, 0}, {"host", 'h', "Host to connect to", (gptr *) &opt_host_name, NULL, NULL, GET_STR_ALLOC, REQUIRED_ARG, 0, 0, 0, 0, 0, 0}, {"password", 'p', "Password", (gptr *) &opt_password, NULL, NULL, GET_STR_ALLOC, OPT_ARG, 0, 0, 0, 0, 0, 0}, {"port", 'P', "Port number", (gptr *) &opt_port_num, NULL, NULL, GET_UINT, REQUIRED_ARG, 0, 0, 0, 0, 0, 0}, {"socket", 'S', "Socket path", (gptr *) &opt_socket_name, NULL, NULL, GET_STR_ALLOC, REQUIRED_ARG, 0, 0, 0, 0, 0, 0}, {"user", 'u', "User name", (gptr *) &opt_user_name, NULL, NULL, GET_STR_ALLOC, REQUIRED_ARG, 0, 0, 0, 0, 0, 0}, { NULL, 0, NULL, NULL, NULL, NULL, GET_NO_ARG, NO_ARG, 0, 0, 0, 0, 0, 0 } }; static void print_error (MYSQL *conn, char *message) { fprintf (stderr, "%s\n", message); if (conn != NULL) { fprintf (stderr, "Error %u (%s): %s\n", mysql_errno (conn), mysql_sqlstate (conn), mysql_error (conn)); } } static my_bool get_one_option (int optid, const struct my_option *opt, char *argument) { switch (optid) { case '?': my_print_help (my_opts); /* print help message */ exit (0); case 'p': /* password */ if (!argument) /* no value given; solicit it later */ ask_password = 1; else /* copy password, wipe out original */ { opt_password = strdup (argument); if (opt_password == NULL) { print_error (NULL, "could not allocate password buffer"); exit (1); } while (*argument) *argument++ = 'x'; ask_password = 0; } break; } return (0); } void print_dashes (MYSQL_RES *res_set) { MYSQL_FIELD *field; unsigned int i, j; mysql_field_seek (res_set, 0); fputc ('+', stdout); for (i = 0; i < mysql_num_fields (res_set); i++) { field = mysql_fetch_field (res_set); for (j = 0; j < field->max_length + 2; j++) fputc ('-', stdout); fputc ('+', stdout); } fputc ('\n', stdout); } void process_result_set (MYSQL *conn, MYSQL_RES *res_set) { MYSQL_ROW row; MYSQL_FIELD *field; unsigned long col_len; unsigned int i; /* determine column display widths -- requires result set to be */ /* generated with mysql_store_result(), not mysql_use_result() */ mysql_field_seek (res_set, 0); for (i = 0; i < mysql_num_fields (res_set); i++) { field = mysql_fetch_field (res_set); col_len = strlen (field->name); if (col_len < field->max_length) col_len = field->max_length; if (col_len < 4 && !IS_NOT_NULL (field->flags)) col_len = 4; /* 4 = length of the word "NULL" */ field->max_length = col_len; /* reset column info */ } print_dashes (res_set); fputc ('|', stdout); mysql_field_seek (res_set, 0); for (i = 0; i < mysql_num_fields (res_set); i++) { field = mysql_fetch_field (res_set); printf (" %-*s |", (int) field->max_length, field->name); } fputc ('\n', stdout); print_dashes (res_set); while ((row = mysql_fetch_row (res_set)) != NULL) { mysql_field_seek (res_set, 0); fputc ('|', stdout); for (i = 0; i < mysql_num_fields (res_set); i++) { field = mysql_fetch_field (res_set); if (row[i] == NULL) /* print the word "NULL" */ printf (" %-*s |", (int) field->max_length, "NULL"); else if (IS_NUM (field->type)) /* print value right-justified */ printf (" %*s |", (int) field->max_length, row[i]); else /* print value left-justified */ printf (" %-*s |", (int) field->max_length, row[i]); } fputc ('\n', stdout); } print_dashes (res_set); printf ("%lu rows returned\n", (unsigned long) mysql_num_rows (res_set)); } void process_multi_statement (MYSQL *conn, char *stmt_str) { MYSQL_RES *res_set; int status; int keep_going = 1; if (mysql_query (conn, stmt_str) != 0) /* the statement(s) failed */ { print_error (conn, "Could not execute statement(s)"); return; } /* the statement(s) succeeded; enter result-retrieval loop */ do { /* determine whether current statement returned data */ res_set = mysql_store_result (conn); if (res_set) /* a result set was returned */ { /* process rows and then free the result set */ process_result_set (conn, res_set); mysql_free_result (res_set); } else /* no result set was returned */ { /* * does the lack of a result set mean that the statement didn't * return one, or that it should have but an error occurred? */ if (mysql_field_count (conn) == 0) { /* * statement generated no result set (it was not a SELECT, * SHOW, DESCRIBE, etc.); just report rows-affected value. */ printf ("%lu rows affected\n", (unsigned long) mysql_affected_rows (conn)); } else /* an error occurred */ { print_error (conn, "Could not retrieve result set"); keep_going = 0; } } /* determine whether more results exist */ /* 0 = yes, -1 = no, >0 = error */ status = mysql_next_result (conn); if (status != 0) /* no more results, or an error occurred */ { keep_going = 0; if (status > 0) /* error */ print_error (conn, "Could not execute statement"); } } while (keep_going); } int main (int argc, char *argv[]) { int opt_err; MY_INIT (argv[0]); load_defaults ("my", client_groups, &argc, &argv); if ((opt_err = handle_options (&argc, &argv, my_opts, get_one_option))) exit (opt_err); /* solicit password if necessary */ if (ask_password) opt_password = get_tty_password (NULL); /* get database name if present on command line */ if (argc > 0) { opt_db_name = argv[0]; --argc; ++argv; } /* initialize connection handler */ conn = mysql_init (NULL); if (conn == NULL) { print_error (NULL, "mysql_init() failed (probably out of memory)"); exit (1); } /* connect to server */ opt_flags |= CLIENT_MULTI_STATEMENTS; if (mysql_real_connect (conn, opt_host_name, opt_user_name, opt_password, opt_db_name, opt_port_num, opt_socket_name, opt_flags) == NULL) { print_error (conn, "mysql_real_connect() failed"); mysql_close (conn); exit (1); } while (1) { char buf[10000]; fprintf (stderr, "query> "); /* print prompt */ if (fgets (buf, sizeof (buf), stdin) == NULL) /* read statement */ break; if (strcmp (buf, "quit\n") == 0 || strcmp (buf, "\\q\n") == 0) break; process_multi_statement (conn, buf); /* execute it */ /* call mysql_changer_user after each query string */ if (mysql_change_user(conn, opt_user_name, opt_password, opt_db_name)) { print_error (conn, "mysql_change_user() failed"); break; } } /* disconnect from server */ mysql_close (conn); exit (0); }