2 # Common MySQL functions
4 BEGIN { push(@INC, ".."); };
7 if ($config{'mysql_libs'}) {
8 $ENV{$gconfig{'ld_env'}} .= ':' if ($ENV{$gconfig{'ld_env'}});
9 $ENV{$gconfig{'ld_env'}} .= $config{'mysql_libs'};
11 if ($config{'mysql'} =~ /^(\S+)\/bin\/mysql$/ && $1 ne '' && $1 ne '/usr') {
12 $ENV{$gconfig{'ld_env'}} .= ':' if ($ENV{$gconfig{'ld_env'}});
13 $ENV{$gconfig{'ld_env'}} .= "$1/lib";
15 if ($module_info{'usermin'}) {
16 # Usermin always runs this module as the logged-in Unix user.
17 # %access is faked up to prevent attempts to create and delete DBs
18 &switch_to_remote_user();
19 &create_user_config_dirs();
20 %access = ( 'create', 0,
25 if ($config{'useident'} ne 'yes') {
26 $mysql_login = $userconfig{'login'};
27 $mysql_pass = $userconfig{'pass'};
29 if (open(VERSION, "$user_module_config_directory/version")) {
30 chop($mysql_version = <VERSION>);
33 $max_dbs = $userconfig{'max_dbs'};
34 $commands_file = "$user_module_config_directory/commands";
35 $sql_charset = $userconfig{'charset'};
36 %displayconfig = %userconfig;
39 # Webmin connects to the database as the user specified in the per-user
41 %access = &get_module_acl();
42 if ($access{'user'} && !$use_global_login) {
43 $mysql_login = $access{'user'};
44 $mysql_pass = $access{'pass'};
47 $mysql_login = $config{'login'};
48 $mysql_pass = $config{'pass'};
50 if (open(VERSION, "$module_config_directory/version")) {
51 chop($mysql_version = <VERSION>);
55 $mysql_version = &get_mysql_version();
57 $cron_cmd = "$module_config_directory/backup.pl";
58 $max_dbs = $config{'max_dbs'};
59 $commands_file = "$module_config_directory/commands";
60 $sql_charset = $config{'charset'};
61 %displayconfig = %config;
63 $authstr = &make_authstr();
65 $password_func = $config{'passwd_mode'} ? "old_password" : "password";
67 @type_list = ('tinyint', 'smallint', 'mediumint', 'int', 'bigint',
68 'float', 'double', 'decimal', 'date', 'datetime', 'timestamp',
69 'time', 'year', 'char', 'varchar', 'tinyblob', 'tinytext',
70 'blob', 'text', 'mediumblob', 'mediumtext', 'longblob', 'longtext',
73 @priv_cols = ('Host', 'User', 'Password', 'Select_priv', 'Insert_priv', 'Update_priv', 'Delete_priv', 'Create_priv', 'Drop_priv', 'Reload_priv', 'Shutdown_priv', 'Process_priv', 'File_priv', 'Grant_priv', 'References_priv', 'Index_priv', 'Alter_priv', 'Show_db_priv', 'Super_priv', 'Create_tmp_table_priv', 'Lock_tables_priv', 'Execute_priv', 'Repl_slave_priv', 'Repl_client_priv', 'Create_view_priv', 'Show_view_priv', 'Create_routine_priv', 'Alter_routine_priv', 'Create_user_priv');
75 if (!$config{'nodbi'}) {
76 # Check if we have DBI::mysql
79 \$driver_handle = DBI->install_driver("mysql");
83 $old_user_priv_cols = $mysql_version >= 4 ? 21 : 14;
84 $old_db_priv_cols = $mysql_version >= 4 ? 12 : 10;
86 @mysql_set_variables = ( "key_buffer", "max_allowed_packet",
87 "sort_buffer", "net_buffer_length",
88 "myisam_sort_buffer_size" );
89 @mysql_number_variables = ( "table_cache", "max_connections",
92 # make_authstr([login], [pass], [host], [port], [sock])
95 local $login = defined($_[0]) ? $_[0] : $mysql_login;
96 local $pass = defined($_[1]) ? $_[1] : $mysql_pass;
97 local $host = defined($_[2]) ? $_[2] : $config{'host'};
98 local $port = defined($_[3]) ? $_[3] : $config{'port'};
99 local $sock = defined($_[4]) ? $_[4] : $config{'sock'};
100 if (&supports_env_pass()) {
101 $ENV{'MYSQL_PWD'} = $pass;
103 return ($sock ? " -S $sock" : "").
104 ($host ? " -h $host" : "").
105 ($port ? " -P $port" : "").
106 ($login ? " -u ".quotemeta($login) : "").
107 (&supports_env_pass() ? "" : # Password comes from environment
108 $pass && $mysql_version >= 4.1 ? " --password=".quotemeta($pass) :
109 $pass ? " -p".quotemeta($pass) : "");
113 # Returns 1 if mysql is running, 0 if not, or -1 if running but
114 # inaccessible without a password. When called in an array context, also
115 # returns the full error message
118 # First type regular connection
119 if ($driver_handle && !$config{'nodbi'}) {
120 local $main::error_must_die = 1;
122 eval { $data = &execute_sql_safe(undef, "select version()") };
124 $err =~ s/\s+at\s+\S+\s+line.*$//;
125 if ($@ =~ /denied|password/i) {
128 elsif ($@ =~ /connect/i) {
131 elsif ($data->{'data'}->[0]->[0] =~ /^\d/) {
135 return wantarray ? ( $rv, $err ) : $rv;
139 # Fall back to mysqladmin command
140 local $out = `"$config{'mysqladmin'}" $authstr status 2>&1`;
141 local $rv = $out =~ /uptime/i ? 1 :
142 $out =~ /denied|password/i ? -1 : 0;
143 $out =~ s/^.*\Q$config{'mysqladmin'}\E\s*:\s*//;
144 return wantarray ? ($rv, $out) : $rv;
148 # Returns a list of all databases
153 # First try using SQL
154 local $main::error_must_die = 1;
155 local $t = &execute_sql_safe($master_db, "show databases");
156 @rv = map { $_->[0] } @{$t->{'data'}};
159 # Fall back to mysqlshow command
160 open(DBS, "\"$config{'mysqlshow'}\" $authstr |");
161 local $t = &parse_mysql_table(DBS);
163 ref($t) || &error("Failed to list databases : $t");
164 @rv = map { $_->[0] } @{$t->{'data'}};
166 return sort { lc($a) cmp lc($b) } @rv;
169 # list_tables(database, [empty-if-denied], [no-filter-views])
170 # Returns a list of tables in some database
173 my ($db, $empty_denied, $include_views) = @_;
176 # First try using SQL
177 local $main::error_must_die = 1;
178 local $t = &execute_sql_safe($db, "show tables");
179 @rv = map { $_->[0] } @{$t->{'data'}};
182 # Fall back to mysqlshow command
183 local $tspec = $db =~ /_/ ? "%" : "";
184 open(DBS, "\"$config{'mysqlshow'}\" $authstr ".
185 quotemeta($db)." $tspec 2>&1 |");
186 local $t = &parse_mysql_table(DBS);
188 if ($t =~ /access denied/i) {
193 &error($text{'edenied'});
197 &error("<tt>".&html_escape($t)."</tt>");
199 @rv = map { $_->[0] } @{$t->{'data'}};
203 if (!$include_views) {
204 if (&supports_views()) {
205 my %views = map { $_, 1 } &list_views($db);
206 @rv = grep { !$views{$_} } @rv;
212 # table_structure(database, table)
213 # Returns a list of hashes detailing the structure of a table
216 local $s = &execute_sql_safe($_[0], "desc "."estr($_[1]));
219 for($i=0; $i<@{$s->{'titles'}}; $i++) {
220 $tp{lc($s->{'titles'}->[$i])} = $i;
223 foreach $r (@{$s->{'data'}}) {
224 push(@rv, { 'field' => $r->[$tp{'field'}],
225 'type' => $r->[$tp{'type'}],
226 'null' => $r->[$tp{'null'}],
227 'key' => $r->[$tp{'key'}],
228 'default' => $r->[$tp{'default'}],
229 'extra' => $r->[$tp{'extra'}],
235 # table_field_sizes(db, table)
236 # Returns a hash mapping field names to sizes
237 sub table_field_sizes
240 foreach my $s (&table_structure(@_)) {
241 if ($s->{'type'} =~ /^\S+\((\d+)(,\d+)?\)/) {
242 $rv{lc($s->{'field'})} = $1;
248 # execute_sql(database, command, [param, ...])
249 # Executes some SQL and returns the results, after checking for the user's
253 return { } if (&is_readonly_mode());
254 return &execute_sql_safe(@_);
257 # execute_sql_safe(database, command, [param, ...])
258 # Executes some SQL and returns the results
262 @params = @_[2..$#_];
263 if ($gconfig{'debug_what_sql'}) {
264 # Write to Webmin debug log
266 for(my $i=0; $i<@params; $i++) {
267 $params .= " ".$i."=".$params[$i];
269 &webmin_debug_log('SQL', "db=$_[0] sql=$sql".$params);
272 if ($driver_handle && !$config{'nodbi'}) {
273 # Use the DBI interface
274 local $cstr = "database=$_[0]";
275 $cstr .= ";host=$config{'host'}" if ($config{'host'});
276 $cstr .= ";port=$config{'port'}" if ($config{'port'});
277 $cstr .= ";mysql_socket=$config{'sock'}" if ($config{'sock'});
278 $cstr .= ";mysql_read_default_file=$config{'my_cnf'}"
279 if (-r $config{'my_cnf'});
280 local $dbh = $driver_handle->connect($cstr, $mysql_login, $mysql_pass,
282 $dbh || &error("DBI connect failed : ",$driver_handle->errstr);
284 # Switch to correct character set
285 local $sql = "set names '$sql_charset'";
286 local $cmd = $dbh->prepare($sql);
288 &error(&text('esql', "<tt>".&html_escape($sql)."</tt>",
289 "<tt>".&html_escape($dbh->errstr)."</tt>"));
291 if (!$cmd->execute()) {
292 &error(&text('esql', "<tt>".&html_escape($sql)."</tt>",
293 "<tt>".&html_escape($dbh->errstr)."</tt>"));
297 local $cmd = $dbh->prepare($sql);
299 &error(&text('esql', "<tt>".&html_escape($_[1])."</tt>",
300 "<tt>".&html_escape($dbh->errstr)."</tt>"));
302 if (!$cmd->execute(@params)) {
303 &error(&text('esql', "<tt>".&html_escape($_[1])."</tt>",
304 "<tt>".&html_escape($dbh->errstr)."</tt>"));
307 local @titles = @{$cmd->{'NAME'}};
308 while(@row = $cmd->fetchrow()) {
309 push(@data, [ @row ]);
313 return { 'titles' => \@titles,
317 # Use the mysql command program
318 local $temp = &transname();
320 # Sub in ? parameters
321 $sql = &replace_sql_parameters($sql, @params);
323 open(TEMP, ">$temp");
325 print TEMP "set names '$sql_charset';\n";
327 print TEMP $sql,"\n";
329 open(DBS, "\"$config{'mysql'}\" $authstr -E -t ".quotemeta($_[0])." <$temp 2>&1 |");
330 local $t = &parse_mysql_vertical(DBS);
334 $t =~ s/^ERROR[^:]*://;
335 &error(&text('esql', "<tt>".&html_escape($_[1])."</tt>",
336 "<tt>".&html_escape($t)."</tt>"));
342 # replace_sql_parameters(sql, params)
343 # Returns a string with ? replaced by parameter text
344 sub replace_sql_parameters
346 my ($sql, @params) = @_;
348 foreach my $p (@params) {
349 $pos = index($sql, '?', $pos+1);
350 &error("Incorrect number of parameters") if ($pos < 0);
353 $qp = $qp eq '' ? 'NULL' : "'$qp'";
354 $sql = substr($sql, 0, $pos).$qp.substr($sql, $pos+1);
355 $pos += length($qp)-1;
360 # execute_sql_logged(database, command, param, ...)
361 # Calls execute_sql, but logs the command first
362 sub execute_sql_logged
364 local ($db, $sql, @params) = @_;
367 local $main::error_must_die = 1;
368 $sql = &replace_sql_parameters($sql, @params);
371 &additional_log('sql', $db, $sql);
372 return &execute_sql(@_);
375 # parse_mysql_table(handle)
376 # Given a filehandle, parses a text table in the format mysql uses
377 sub parse_mysql_table
380 local ($line, $i, @edge);
384 return $line if ($line =~ /^(ERROR|\S*mysqlshow:)/);
385 } while($line && $line !~ /^\+/);
386 for($i=0; $i<length($line); $i++) {
387 push(@edge, $i) if (substr($line, $i, 1) eq '+');
389 $line = <$fh>; # skip first row of -'s
390 local @titles = &parse_mysql_line($line, \@edge);
391 $line = <$fh>; # skip next row of -'s
395 last if (!$line || $line !~ /^\|/);
396 while($line !~ /\|\s+$/) {
397 # Line has a return in it!
400 push(@data, [ &parse_mysql_line($line, \@edge) ]);
402 return { 'titles' => \@titles,
406 # parse_mysql_line(line, &edges)
410 for($i=0; $i<@{$_[1]}-1; $i++) {
411 local $w = substr($_[0], $_[1]->[$i]+1,
412 $_[1]->[$i+1] - $_[1]->[$i] - 2);
422 # parse_mysql_vertical(handle)
423 # Parses mysql output in the -E format
424 sub parse_mysql_vertical
426 local (@data, @titles, $row = -1, $col, %hascol);
430 # No output at all - must be a non-select
433 return $line if ($line =~ /^ERROR/);
434 local $errtxt = &text('eparse', "<tt>mysql</tt>", "<tt>DBI</tt>",
435 "<tt>DBD::mysql</tt>");
438 if ($line =~ /^\*\*\*/) {
444 elsif ($line =~ /^\s*([^:\s]+): (.*)/ && ($row == 0 || $hascol{$1})) {
448 $row >= 0 || return $errtxt;
449 $data[$row]->[$col] = $2;
453 # Continuing the last column
454 $row >= 0 || return $errtxt;
455 $data[$row]->[$col] .= "\n".$line;
459 return { 'titles' => \@titles,
465 if ($module_info{'usermin'}) {
466 foreach $l (split(/\t/, $config{'access'})) {
467 if ($l =~ /^(\S+):\s*(.*)$/ &&
468 ($1 eq $remote_user || $1 eq '*')) {
469 local @dbs = split(/\s+/, $2);
472 $d =~ s/\$REMOTE_USER/$remote_user/g;
473 return 1 if ($d eq '*' || $_[0] =~ /^$d$/);
482 return 1 if ($access{'dbs'} eq '*');
483 foreach $d (split(/\s+/, $access{'dbs'})) {
484 return 1 if ($d && $d eq $_[0]);
490 # supports_backup_db(name)
491 # Returns 1 if some database can be backed up
492 sub supports_backup_db
494 return $_[0] ne "information_schema" &&
495 $_[0] ne "performance_schema";
498 # list_accessible_databases()
499 # Returns a list of databases that the current user may access to. Returns
500 # an empty list if he has all of them.
501 sub list_accessible_databases
503 if ($module_info{'usermin'}) {
506 foreach $l (split(/\t/, $config{'access'})) {
507 if ($l =~ /^(\S+):\s*(.*)$/ &&
508 ($1 eq $remote_user || $1 eq '*')) {
509 push(@rv, split(/\s+/, $2));
515 # From Webmin access control list
516 return ( ) if ($access{'dbs'} eq '*');
517 return split(/\s+/, $access{'dbs'});
521 # split_table(&titles, &checkboxes, &links, &col1, &col2, ...)
522 # Outputs a table that is split into two parts
525 local $mid = int((@{$_[2]}+1) / 2);
527 print "<table width=100%><tr>\n";
528 foreach $s ([0, $mid-1], [$mid, @{$_[2]}-1]) {
529 print "<td width=50% valign=top>\n";
532 local @tds = $_[1] ? ( "width=5" ) : ( );
533 if ($s->[0] <= $s->[1]) {
535 foreach $t (@{$_[0]}) {
538 print &ui_columns_start(\@hcols, 100, 0, \@tds);
541 for($i=$s->[0]; $i<=$s->[1]; $i++) {
543 push(@cols, "<a href='$_[2]->[$i]'>$_[3]->[$i]</a>");
544 for($j=4; $j<@_; $j++) {
545 push(@cols, $_[$j]->[$i]);
548 print &ui_checked_columns_row(\@cols, \@tds, "d", $_[1]->[$i]);
551 print &ui_columns_row(\@cols, \@tds);
554 if ($s->[0] <= $s->[1]) {
555 print &ui_columns_end();
559 print "</tr></table>\n";
566 local @dbs = &list_databases();
568 if ($access{'perms'} == 2 && $access{'dbs'} ne '*') {
569 # Can only select his own databases
570 $rv = &ui_select("dbs", $_[0],
571 [ grep { &can_edit_db($_) } @dbs ], 1, 0, $_[0] ? 1 : 0);
574 # Can select any databases
575 local $ind = &indexof($_[0],@dbs) >= 0;
576 $rv = &ui_radio("db_def", $_[0] eq '%' || $_[0] eq '' ? 1 :
578 [ [ 1, $text{'host_any'} ],
579 [ 2, $text{'host_sel'}." ".
580 &ui_select("dbs", $_[0], \@dbs) ],
581 [ 0, $text{'host_otherdb'}." ".
582 &ui_textbox("db", $_[0] eq '%' || $_[0] eq '' ||
583 $ind ? '' : $_[0], 30) ] ]);
590 return "estr($_[0]);
597 if (&supports_quoting()) {
606 # Returns a string with quotes escaped, for use in SQL
615 # Returns 1 if running mysql version 3.23.6 or later
618 if ($mysql_version =~ /^(\d+)\.(\d+)\.(\d+)$/ &&
619 ($1 > 3 || ($1 == 3 && $2 > 23) || ($1 == 3 && $2 == 23 && $3 >= 6))) {
622 elsif ($mysql_version > 4) {
630 # supports_routines()
631 # Returns 1 if mysqldump supports routines
632 sub supports_routines
634 local $out = &backquote_command("$config{'mysqldump'} --help 2>&1 </dev/null");
635 return $out =~ /--routines/ ? 1 : 0;
639 # Returns 1 if this MySQL install supports views
642 if (!defined($supports_views_cache)) {
643 if ($mysql_version < 5) {
644 $supports_views_cache = 0;
647 local @dbs = &list_databases();
648 if (&indexof("information_schema", @dbs) >= 0) {
649 # Has information_schema DB .. but does it have views?
650 local @ist = &list_tables("information_schema", 1, 1);
651 if (&indexoflc("views", @ist) >= 0) {
652 $supports_views_cache = 1;
655 $supports_views_cache = 0;
659 $supports_views_cache = 0;
663 return $supports_views_cache;
666 # supports_variables()
667 # Returns 1 if running mysql version 4.0.3 or later
668 sub supports_variables
670 if ($mysql_version =~ /^(\d+)\.(\d+)\.(\d+)$/ &&
671 ($1 > 4 || ($1 == 4 && $2 > 0) || ($1 == 4 && $2 == 0 && $3 >= 3))) {
674 elsif ($mysql_version > 4) {
682 # supports_env_pass()
683 # Returns 1 if passing the password via an environment variable is supported
684 sub supports_env_pass
686 if ($mysql_version >= 4.1 && !$config{'nopwd'}) {
687 # Theortically possible .. but don't do this if ~/.my.cnf contains
688 # a [client] block with password= in it
689 my @uinfo = getpwuid($<);
690 foreach my $cf ($config{'my_cnf'}, "$uinfo[7]/.my.cnf",
691 "$ENV{'HOME'}/.my.cnf") {
692 next if (!$cf || !-r $cf);
693 local @cf = &parse_mysql_config($cf);
694 local $client = &find("client", \@cf);
696 local $password = &find("password", $client->{'members'});
697 return 0 if ($password ne '');
705 # Returns 1 if MYSQL_PWD can be used to pass the password to mysql
708 return 1 if (!&supports_env_pass()); # Not even used
709 local $config{'nodbi'} = 1;
711 local $main::error_must_die = 1;
712 eval { $data = &execute_sql_safe(undef, "select version()") };
713 return $@ || !$data ? 0 : 1;
717 # Returns the number of columns used for privileges in the user table
720 if (!$user_priv_cols) {
721 local @str = &table_structure("mysql", "user");
724 $user_priv_cols++ if ($s->{'field'} =~ /_priv/i);
727 return $user_priv_cols;
731 # Returns the number of columns used for privileges in the db table
734 if (!$db_priv_cols) {
735 local @str = &table_structure("mysql", "db");
738 $db_priv_cols++ if ($s->{'field'} =~ /_priv/i);
741 return $db_priv_cols;
745 # Returns the number of columns used for privileges in the db table
748 if (!$host_priv_cols) {
749 local @str = &table_structure("mysql", "host");
752 $host_priv_cols++ if ($s->{'field'} =~ /_priv/i);
755 return $host_priv_cols;
760 return $_[0]->{'type'} =~ /(text|blob)$/i;
763 # get_mysql_version(&out)
764 # Returns a version number, undef if one cannot be found, or -1 for a .so
766 sub get_mysql_version
768 local $out = &backquote_command("\"$config{'mysql'}\" -V 2>&1");
769 ${$_[0]} = $out if ($_[0]);
770 if ($out =~ /lib\S+\.so/) {
773 elsif ($out =~ /distrib\s+((3|4|5|6)\.[0-9\.]*)/i) {
781 # date_subs(filename)
782 # Does strftime-style date substitutions on a filename, if enabled
785 if ($config{'date_subs'}) {
787 eval "use posix" if ($@);
788 local @tm = localtime(time());
789 &clear_time_locale();
790 local $rv = strftime($_[0], @tm);
791 &reset_time_locale();
799 # execute_before(db, handle, escape, path, db-for-config)
800 # Executes the before-backup command for some DB, and sends output to the
801 # given file handle. Returns 1 if the command suceeds, or 0 on failure
804 local $cmd = $config{'backup_before_'.$_[4]};
806 $ENV{'BACKUP_FILE'} = $_[3];
809 local $rv = &execute_command($cmd, undef, \$out, \$out);
811 print $h $_[2] ? "<pre>".&html_escape($out)."</pre>" : $out;
818 # execute_after(db, handle, escape, path, db-for-config)
821 local $cmd = $config{'backup_after_'.$_[4]};
823 $ENV{'BACKUP_FILE'} = $_[3];
826 local $rv = &execute_command($cmd, undef, \$out, \$out);
828 print $h $_[2] ? "<pre>".&html_escape($out)."</pre>" : $out;
835 # show_table_form(count)
838 print &ui_columns_start([ $text{'field_name'}, $text{'field_type'},
839 $text{'field_size'}, $text{'table_nkey'},
840 $text{'field_auto'}, $text{'field_null'},
841 $text{'field_unsigned'}, $text{'field_default'} ]);
843 for($i=0; $i<$_[0]; $i++) {
845 push(@cols, &ui_textbox("field_$i", undef, 20));
846 push(@cols, &ui_select("type_$i", "", [ "", @type_list ]));
847 push(@cols, &ui_textbox("size_$i", undef, 10));
848 push(@cols, &ui_checkbox("key_$i", 1, $text{'yes'}, 0));
849 push(@cols, &ui_checkbox("auto_$i", 1, $text{'yes'}, 0));
850 push(@cols, &ui_checkbox("null_$i", 1, $text{'yes'}, 1));
851 push(@cols, &ui_checkbox("unsigned_$i", 1, $text{'yes'}, 0));
852 push(@cols, &ui_textbox("default_$i", undef, 20));
853 print &ui_columns_row(\@cols);
855 print &ui_columns_end();
858 # parse_table_form(&extrafields, tablename)
861 local @fields = @{$_[0]};
864 for($i=0; defined($in{"field_$i"}); $i++) {
865 next if (!$in{"field_$i"});
866 $in{"field_$i"} =~ /^\S+$/ ||
867 &error(&text('table_efield', $in{"field_$i"}));
868 $in{"type_$i"} || &error(&text('table_etype', $in{"field_$i"}));
869 if ($in{"type_$i"} eq 'enum' || $in{"type_$i"} eq 'set') {
870 local @ev = split(/\s+/, $in{"size_$i"});
871 @ev || &error(&text('table_eenum', $in{"type_$i"},
873 $in{"size_$i"} = join(",", map { "'$_'" } @ev);
875 if ($in{"size_$i"}) {
876 push(@fields, sprintf "%s %s(%s)",
877 "estr($in{"field_$i"}), $in{"type_$i"},$in{"size_$i"});
880 push(@fields, sprintf "%s %s",
881 "estr($in{"field_$i"}), $in{"type_$i"});
883 if ($in{"unsigned_$i"}) {
884 $fields[@fields-1] .= " unsigned";
886 if (!$in{"null_$i"}) {
887 $fields[@fields-1] .= " not null";
890 $in{"null_$i"} && &error(&text('table_epnull',$in{"field_$i"}));
891 push(@pri, $in{"field_$i"});
893 if ($in{"auto_$i"}) {
894 push(@auto, $fields[@fields-1]);
895 push(@autokey, $in{"key_$i"});
897 if ($in{"default_$i"}) {
898 $fields[@fields-1] .= " default '".$in{"default_$i"}."'";
901 @auto < 2 || &error($text{'table_eauto'});
902 @fields || &error($text{'table_enone'});
904 local $sql = "create table "."estr($_[1])." (".join(",", @fields).")";
905 $sql .= " type = $in{'type'}" if ($in{'type'});
908 # Setup primary fields too
909 push(@sql, "alter table "."estr($_[1])." add primary key (".
910 join(",", map { "estr($_) } @pri).")");
913 # Make field auto-increment
914 push(@sql, "alter table "."estr($_[1]).
915 " modify $auto[0] auto_increment ".
916 ($autokey[0] ? "" : "unique"));
921 # execute_sql_file(database, file, [user, pass], [unix-user])
922 # Executes some file of SQL commands, and returns the exit status and output
925 if (&is_readonly_mode()) {
928 local ($db, $file, $user, $pass) = @_;
929 local $authstr = &make_authstr($user, $pass);
930 local $cmd = "$config{'mysql'} $authstr -t ".quotemeta($db)." <".quotemeta($file);
931 -r $file || return (1, "$file does not exist");
932 if ($_[4] && $_[4] ne 'root' && $< == 0) {
933 # Restoring as a Unix user
934 $cmd = &command_as_user($_[4], 0, $cmd);
936 local $out = &backquote_logged("$cmd 2>&1");
937 local @rv = ($?, $? ? $out || "$cmd failed" : $out);
938 &make_authstr(); # Put back old password environment variable
943 # Starts the MySQL database server, and returns undef on success or an
944 # error message on failure.
947 local $temp = &transname();
948 local $rv = &system_logged("($config{'start_cmd'}) >$temp 2>&1");
949 local $out = `cat $temp`; unlink($temp);
950 if ($rv || $out =~ /failed/i) {
951 return "<pre>$out</pre>";
957 # Halts the MySQL database server, and returns undef on success or an
958 # error message on failure.
962 if ($config{'stop_cmd'}) {
963 $out = &backquote_logged("$config{'stop_cmd'} 2>&1");
966 $out = &backquote_logged("$config{'mysqladmin'} $authstr shutdown 2>&1");
968 if ($? || $out =~ /failed/i) {
969 return "<pre>$out</pre>";
975 # Returns a list of allowed values for an enum
979 if ($type =~ /^(enum|set)\((.*)\)$/) {
982 local $esize = $type;
984 while($esize =~ /^'([^']*)'(,?)(.*)$/) {
991 # Returns 1 if the mysql server being managed is on this system
994 return $config{'host'} eq '' || $config{'host'} eq 'localhost' ||
995 $config{'host'} eq &get_system_hostname() ||
996 &to_ipaddress($config{'host'}) eq &to_ipaddress(&get_system_hostname());
1000 # Returns the parsed my.cnf file
1001 sub get_mysql_config
1003 if (!scalar(@mysql_config_cache)) {
1004 if (!-r $config{'my_cnf'}) {
1007 @mysql_config_cache = &parse_mysql_config($config{'my_cnf'});
1009 return \@mysql_config_cache;
1012 # parse_mysql_config(file)
1013 # Reads one MySQL config file
1014 sub parse_mysql_config
1020 local $lref = &read_file_lines($file, 1);
1026 if (/^\s*\[(\S+)\]$/) {
1027 # Start of a section
1028 $sect = { 'name' => $1,
1035 elsif (/^\s*(\S+)\s*=\s*(.*)$/ && $sect) {
1036 # Variable in a section
1037 push(@{$sect->{'members'}},
1042 $sect->{'eline'} = $lnum;
1044 elsif (/^\s*(\S+)$/ && $sect) {
1045 # Single directive in a section
1046 push(@{$sect->{'members'}},
1050 $sect->{'eline'} = $lnum;
1060 local ($name, $conf) = @_;
1061 local @rv = grep { lc($_->{'name'}) eq lc($name) } @$conf;
1062 return wantarray ? @rv : $rv[0];
1065 # find_value(name, &conf)
1068 local ($name, $conf) = @_;
1069 local @rv = map { $_->{'value'} } &find($name, $conf);
1070 return wantarray ? @rv : $rv[0];
1073 # save_directive(&conf, §ion, name, &values)
1074 # Updates one or multiple lines in a my.cnf section
1077 local ($conf, $sect, $name, $values) = @_;
1078 local @old = &find($name, $sect->{'members'});
1079 local $lref = &read_file_lines($config{'my_cnf'});
1081 for(my $i=0; $i<@old || $i<@$values; $i++) {
1082 local $old = $old[$i];
1083 local $line = $values->[$i] eq "" ? $name :
1084 "$name = $values->[$i]";
1085 if ($old && defined($values->[$i])) {
1087 $lref->[$old->{'line'}] = $line;
1088 $old->{'value'} = $values->[$i];
1090 elsif (!$old && defined($values->[$i])) {
1092 splice(@$lref, $sect->{'eline'}+1, 0, $line);
1093 &renumber($conf, $sect->{'eline'}+1, 1);
1094 push(@{$sect->{'members'}},
1096 'value' => $values->[$i],
1097 'line' => $sect->{'eline'}+1 });
1099 elsif ($old && !defined($values->[$i])) {
1101 splice(@$lref, $old->{'line'}, 1);
1102 &renumber($conf, $old->{'line'}, -1);
1103 @{$sect->{'members'}} = grep { $_ ne $old }
1104 @{$sect->{'members'}};
1111 local ($conf, $line, $offset) = @_;
1112 foreach my $sect (@$conf) {
1113 $sect->{'line'} += $offset if ($sect->{'line'} >= $line);
1114 $sect->{'eline'} += $offset if ($sect->{'eline'} >= $line);
1115 foreach my $m (@{$sect->{'members'}}) {
1116 $m->{'line'} += $offset if ($m->{'line'} >= $line);
1121 # parse_set_variables(value, ...)
1122 # Returns a hash of variable mappings
1123 sub parse_set_variables
1126 foreach my $v (@_) {
1127 if ($v =~ /^(\S+)=(\S+)$/) {
1134 sub mysql_size_input
1136 local ($name, $value) = @_;
1138 if ($value =~ /^(\d+)([a-z])$/i) {
1142 $units = "" if ($units eq "b");
1143 return &ui_textbox($name, $value, 8)."\n".
1144 &ui_select($name."_units", $units,
1145 [ [ "", "bytes" ], [ "K", "kB" ],
1146 [ "M", "MB" ], [ "G", "GB" ] ]);
1150 # Returns the names of all indexes in some database
1155 foreach my $table (&list_tables($db)) {
1156 local $s = &execute_sql_safe($db, "show index from "."estr($table));
1158 for($i=0; $i<@{$s->{'titles'}}; $i++) {
1159 $tp{lc($s->{'titles'}->[$i])} = $i;
1161 foreach $r (@{$s->{'data'}}) {
1162 if ($r->[$tp{'key_name'}] ne 'PRIMARY') {
1163 push(@rv, $r->[$tp{'key_name'}]);
1167 return &unique(@rv);
1170 # index_structure(db, indexname)
1171 # Returns information on an index
1174 local ($db, $index) = @_;
1177 foreach my $table (&list_tables($db)) {
1178 local $s = &execute_sql_safe($db, "show index from "."estr($table));
1180 for($i=0; $i<@{$s->{'titles'}}; $i++) {
1181 $tp{lc($s->{'titles'}->[$i])} = $i;
1183 foreach $r (@{$s->{'data'}}) {
1184 if ($r->[$tp{'key_name'}] eq $index) {
1186 $info->{'table'} = $r->[$tp{'table'}];
1187 $info->{'name'} = $index;
1188 $info->{'type'} = lc($r->[$tp{'index_type'}]) ||
1189 lc($r->[$tp{'comment'}]);
1190 push(@{$info->{'cols'}}, $r->[$tp{'column_name'}]);
1198 # Returns the names of all views in some database
1203 local $d = &execute_sql($db, "select table_schema,table_name from information_schema.views where table_schema = ?", $db);
1204 foreach $r (@{$d->{'data'}}) {
1210 # view_structure(db, viewname)
1211 # Returns information about a view
1214 local ($db, $view) = @_;
1215 local $info = { 'name' => $view };
1216 local $d = &execute_sql($db, "show create view $view");
1217 local $c = $d->{'data'}->[0]->[1];
1218 if ($c =~ /algorithm\s*=\s*(\S+)/i) {
1219 $info->{'algorithm'} = lc($1);
1221 if ($c =~ /definer\s*=\s*`(\S+)`\@`(\S+)`/i) {
1222 $info->{'definer'} = "$1\@$2";
1224 elsif ($c =~ /definer\s*=\s*(\S+)/i) {
1225 $info->{'definer'} = $1;
1227 if ($c =~ /sql\s+security\s+(\S+)/i) {
1228 $info->{'security'} = lc($1);
1230 if ($c =~ s/\s+with\s+(cascaded|local)\s+check\s+option//i) {
1231 $info->{'check'} = lc($1);
1233 if ($c =~ /view\s+(`\S+`|\S+)\s+as\s+(.*)/i) {
1234 $info->{'query'} = $2;
1239 # list_character_sets([db])
1240 # Returns a list of supported character sets. Each row is an array ref of
1242 sub list_character_sets
1245 local $db = $_[0] || $master_db;
1246 if ($mysql_version < 4.1) {
1247 local $d = &execute_sql($db, "show variables like 'character_sets'");
1248 @rv = map { [ $_, $_ ] } split(/\s+/, $d->{'data'}->[0]->[1]);
1251 local $d = &execute_sql($db, "show character set");
1252 @rv = map { [ $_->[0], $_->[1] ] } @{$d->{'data'}};
1254 return sort { lc($a->[1]) cmp lc($b->[1]) } @rv;
1257 # list_collation_orders([db])
1258 # Returns a list of supported collation orders. Each row is an array ref of
1259 # a code and character set it can work with.
1260 sub list_collation_orders
1263 local $db = $_[0] || $master_db;
1264 if ($mysql_version >= 5) {
1265 local $d = &execute_sql($db, "show collation");
1266 @rv = map { [ $_->[0], $_->[1] ] } @{$d->{'data'}};
1268 return sort { lc($a->[0]) cmp lc($b->[0]) } @rv;
1271 # list_system_variables()
1272 # Returns a list of all system variables, and their default values
1273 sub list_system_variables
1275 local $mysqld = $config{'mysqld'};
1277 # Mysqld path not in config .. guess from mysql path
1278 $mysqld = $config{'mysql'};
1279 $mysqld =~ s/mysql$/mysqld/g;
1280 $mysqld =~ s/bin/sbin/g;
1282 $mysqld = $config{'mysql'};
1283 $mysqld =~ s/mysql$/mysqld/g;
1284 $mysqld =~ s/bin/libexec/g;
1286 # Look in Webmin path
1288 $mysqld = &has_command("mysqld");
1292 return ( ) if (!$mysqld);
1294 # Read supported variables
1296 &open_execute_command(MYSQLD, "$mysqld --verbose --help", 1, 1);
1299 if (/^(\S+)\s+current\s+value:\s+(\S*)/) {
1300 push(@rv, [ $1, $2 ]);
1302 elsif (/^\-\-\-\-/) {
1305 elsif ($started && /^(\S+)\s+(.*)/) {
1306 push(@rv, [ $1, $2 eq "(No default value)" ? undef : $2 ]);
1313 # list_compatible_formats()
1314 # Returns a list of two-element arrays, containing compatability format
1315 # codes and descriptions
1316 sub list_compatible_formats
1318 return map { [ $_, $text{'compat_'.$_} ] }
1319 ( "ansi", "mysql323", "mysql40", "postgresql", "oracle", "mssql",
1323 # list_compatible_options()
1324 # Returns a list of two-element arrays, containing compatability options
1325 sub list_compatible_options
1327 return map { [ $_, $text{'compat_'.$_} ] }
1328 ( "no_key_options", "no_table_options", "no_field_options" );
1331 # compression_format(file)
1332 # Returns 0 if uncompressed, 1 for gzip, 2 for compress, 3 for bzip2 or
1334 sub compression_format
1336 open(BACKUP, $_[0]);
1338 read(BACKUP, $two, 2);
1340 return $two eq "\037\213" ? 1 :
1341 $two eq "\037\235" ? 2 :
1343 $two eq "BZ" ? 3 : 0;
1346 # backup_database(db, dest-file, compress-mode, drop-flag, where-clause,
1347 # charset, &compatible, &only-tables, run-as-user,
1348 # single-transaction-flag)
1349 # Backs up a database to the given file, optionally with compression. Returns
1350 # undef on success, or an error message on failure.
1353 local ($db, $file, $compress, $drop, $where, $charset, $compatible,
1354 $tables, $user, $single) = @_;
1355 if ($compress == 0) {
1356 $writer = ">".quotemeta($file);
1358 elsif ($compress == 1) {
1359 $writer = "| gzip -c >".quotemeta($file);
1361 elsif ($compress == 2) {
1362 $writer = "| bzip2 -c >".quotemeta($file);
1364 local $dropsql = $drop ? "--add-drop-table" : "";
1365 local $singlesql = $single ? "--single-transaction" : "";
1366 local $wheresql = $where ? "\"--where=$in{'where'}\"" : "";
1367 local $charsetsql = $charset ?
1368 "--default-character-set=".quotemeta($charset) : "";
1369 local $compatiblesql = @$compatible ?
1370 "--compatible=".join(",", @$compatible) : "";
1371 local $quotingsql = &supports_quoting() ? "--quote-names" : "";
1372 local $routinessql = &supports_routines() ? "--routines" : "";
1373 local $tablessql = join(" ", map { quotemeta($_) } @$tables);
1374 local $cmd = "$config{'mysqldump'} $authstr $dropsql $singlesql $wheresql $charsetsql $compatiblesql $quotingsql $routinessql ".quotemeta($db)." $tablessql 2>&1 $writer";
1375 if ($user && $user ne "root") {
1376 $cmd = &command_as_user($user, undef, $cmd);
1378 local $out = &backquote_logged("($cmd) 2>&1");