Hello all -

I have an Innodb database with some VIEWs in it. I'm using mysqldump
to back it up. However, after looking at the results file, the VIEWs
are simply table definitions. I've looked at the man page for
mysqldump, and it asked me to look at "Restrictions on Views", which
doesn't contain anything about mysqldump.

In phpMyAdmin, the 'Export' tab correctly dumps a CREATE VIEW
statement. Is there a switch that I'm missing in my backup script?

An addendum -

Actually, the results from the mysqldump do contain the statements to
crew the views, but they are commented out. How do I enable them?

On Tue, 12 Feb 2008 10:11:23 -0800 (PST), lawpoop@gmail.com wrote:
> An addendum -
>
> Actually, the results from the mysqldump do contain the statements to
> crew the views, but they are commented out. How do I enable them?

Want a wild-assed guess? Uncomment them.

--
Remember, a 12'x12'x18" raised floor can hold over a thousand gallons of
blood before it starts to seep up through the cracks.
-- Roger Burton West in the Monastery

On Feb 12, 12:51 pm, "Peter H. Coffin" <hell...@ninehells.com> wrote:
> On Tue, 12 Feb 2008 10:11:23 -0800 (PST), lawp...@gmail.com wrote:
> > An addendum -
>
> > Actually, the results from the mysqldump do contain the statements to
> > crew the views, but they are commented out. How do I enable them?
>
> Want a wild-assed guess? Uncomment them.

OK, you got me.

What I am really looking for is a way to get my database back to the
state as simply as possible.

Since I need to create a 'restore to point in past' for my users, I
can't really have them opening up a 90+ MB text file and properly
uncommenting a few SQL statements at the very end of the file. What I
need is a single file of SQL statements that re-create the database at
the point of the dump.

My users need to be able to click on a button and have the database
restore being run.

Do you have any more-refined guesses?

>
> --
> Remember, a 12'x12'x18" raised floor can hold over a thousand gallons of
> blood before it starts to seep up through the cracks.
> -- Roger Burton West in the Monastery

On Tue, 12 Feb 2008 11:21:57 -0800 (PST),
lawpoop@gmail.com wrote:

>On Feb 12, 12:51 pm, "Peter H. Coffin" <hell...@ninehells.com> wrote:
>> On Tue, 12 Feb 2008 10:11:23 -0800 (PST), lawp...@gmail.com wrote:
>> > An addendum -
>>
>> > Actually, the results from the mysqldump do contain the statements to
>> > crew the views, but they are commented out. How do I enable them?
>>
>> Want a wild-assed guess? Uncomment them.
>
>OK, you got me.
>
>What I am really looking for is a way to get my database back to the
>state as simply as possible.
>
>Since I need to create a 'restore to point in past' for my users, I
>can't really have them opening up a 90+ MB text file and properly
>uncommenting a few SQL statements at the very end of the file. What I
>need is a single file of SQL statements that re-create the database at
>the point of the dump.
>
>My users need to be able to click on a button and have the database
>restore being run.
>
>Do you have any more-refined guesses?

If it's commented out like this:

/*![version number] CREATE VIEW statement */

a proper version of MySQL will still use the statement.
Experiment to see if that works for you.

http://dev.mysql.com/doc/refman/5.0/en/comments.html
states:

" MySQL Server supports some variants of C-style comments.
These enable you to write code that includes MySQL
extensions, but is still portable, by using comments of
the following form:

/*! MySQL-specific code */

In this case, MySQL Server parses and executes the code
within the comment as it would any other SQL statement,
but other SQL servers will ignore the extensions. For
example, MySQL Server recognizes the STRAIGHT_JOIN keyword
in the following statement, but other servers will not:

SELECT /*! STRAIGHT_JOIN */ col1 FROM table1,table2 WHERE
....

If you add a version number after the “!� character, the
syntax within the comment is executed only if the MySQL
version is greater than or equal to the specified version
number. The TEMPORARY keyword in the following comment is
executed only by servers from MySQL 3.23.02 or higher:

CREATE /*!32302 TEMPORARY */ TABLE t (a INT); "


Also, make sure to use the most recent version of
mysqldump and experiment with options.

http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html
says:

" Note
Prior to release 5.0.48, [the --compact option, which
triggers several --skip-* options] did not create valid
SQL if the database dump contained views. The recreation
of views requires the creation and removal of temporary
tables and this option suppressed the removal of those
temporary tables. As a workaround, use --compress with the
--add-drop-table option and then manually adjust the dump
file.
"

--
( Kees
)
c[_] It is bad luck to be superstitious. (#71)

lawpoop@gmail.com wrote:

> I have an Innodb database with some VIEWs in it. I'm using mysqldump
> to back it up. However, after looking at the results file, the VIEWs
> are simply table definitions.

Look closer. Look at the end of the dump. The VIEW definitions are
there. For reasons I don't want to talk about views are created
as base tables first, then dropped and recreated as real views.

Dont't ask!


XL
--
Axel Schwenke, Support Engineer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/

On Feb 12, 5:36 pm, Axel Schwenke <axel.schwe...@gmx.de> wrote:
> lawp...@gmail.com wrote:
> > I have an Innodb database with some VIEWs in it. I'm using mysqldump
> > to back it up. However, after looking at the results file, the VIEWs
> > are simply table definitions.
>
> Look closer. Look at the end of the dump. The VIEW definitions are
> there. For reasons I don't want to talk about views are created
> as base tables first, then dropped and recreated as real views.
>
> Dont't ask!
>
> XL
> --
> Axel Schwenke, Support Engineer, MySQL AB
>
> Online User Manual:http://dev.mysql.com/doc/refman/5.0/en/
> MySQL User Forums: http://forums.mysql.com/

I'll do some more testing, but so far, when I ran the backup files
( using the \. operator ), the views remained as tables after the file
finished loading.

I'm redoing my testing to make sure that I can re-create it, and to
post my command sequence to the newsgroup.

OK, here's what I've got:

I have a database with several views defined. I made a dump of this
database using this mysqldump command:

Then I go into the mysql command line. I verify the definition of the
view in the original database, and select some records from it. Then,
I create a database called test, run the backup file, check the
definition of a view, and select records. The view was created as a
MyISAM table, and it has no records. Also, what is interesting to note
is that there are errors when MySQL is loading the backup file. These
errors reference the names of the views.


mysql> use original_database
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SHOW CREATE TABLE vHistoricalStoreScores;
+------------------------
+-----------------------------------------------------------------------
| View | Create
View
+------------------------
+------------------------------------------------------------------------
| vHistoricalStoreScores |CREATE ALGORITHM=UNDEFINED
DEFINER=`restcon`@`64.111.96.0/255.255.224.0` SQL SECURITY DEFINER
VIEW `vHistoricalStoreScores` AS select ...


mysql> SELECT * FROM vHistoricalStoreScores;
+------------------+--------------------+-----------+----------------
+------------------+-------+------+
| compilation | client_location_id | client_id | client_name |
location | month | year |
+------------------+--------------------+-----------+----------------
+------------------+-------+------+
| 4.45833333333333 | 5 | 2 | Client 2 |
Main St. | 1 | 2008 |
| 4.26388888888889 | 1 | 1 | Test Client |
Test Location | 1 | 2008 |
| 4.09027777777778 | 5 | 2 | Client 2 |
Main St. | 12 | 2007 |
+------------------+--------------------+-----------+----------------
+------------------+-------+------+
14 rows in set (0.03 sec)


mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.00 sec)

mysql> USE test;
Database changed

mysql> \. original_database_backup.sql


Query OK, 0 rows affected (0.06 sec)
Query OK, 0 rows affected (0.06 sec)
....
Query OK, 0 rows affected (0.06 sec)
ERROR 1050 (42S01): Table 'vCardQuestionsNested' already exists
ERROR 1050 (42S01): Table 'vClientLocationCodeDates' already exists
ERROR 1050 (42S01): Table 'vClientLocationQuestions' already exists
ERROR 1050 (42S01): Table 'vClientLocationQuestionsNested' already
exists
ERROR 1050 (42S01): Table 'vClientQuestionsNested' already exists
ERROR 1050 (42S01): Table 'vHistoricalStoreScores' already exists
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW CREATE TABLE vHistoricalStoreScores;
+------------------------
+----------------------------------------------
| Table | Create
Table
|
+------------------------
+----------------------------------------------
| vHistoricalStoreScores | CREATE TABLE `vHistoricalStoreScores` (
`compilation` double default NULL,
`client_location_id` int(10) unsigned default NULL,
`client_id` int(10) unsigned default NULL,
`client_name` varchar(40) default NULL,
`location` varchar(30) default NULL,
`month` bigint(2) default NULL,
`year` bigint(4) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+------------------------
+-----------------------------------------------
1 row in set (0.00 sec)

mysql> SELECT * FROM vHistoricalStoreScores;
Empty set (0.00 sec)

On Feb 12, 5:36 pm, Axel Schwenke <axel.schwe...@gmx.de> wrote:
>
> Look closer. Look at the end of the dump. The VIEW definitions are
> there. For reasons I don't want to talk about views are created
> as base tables first, then dropped and recreated as real views.
>
> Dont't ask!
>
> XL

OK, here's what I've got. I have a database with several views in it.
I made a dump using the following command:

mysqldump -uuser -p - --skip-opt --all --complete-insert
original_database > ./original_database_backup.sql

Then, I log on to the mysql command line. I test the definition of a
view and select some records from it. After that, I create a database
called test, and load the backup file into it. What's interesting to
note that that errors are thrown, claiming that tables with the names
of the views already exist. Then, I look at the definition of the view
in the table, and it's a regular MyISAM table, with no records.


mysql> use original_database
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SHOW CREATE TABLE vHistoricalStoreScores;
+------------------------
+-----------------------------------------------------------------------
| View | Create
View
|
+------------------------
+--------------------------------------------------------------------------
+
| vHistoricalStoreScores |CREATE ALGORITHM=UNDEFINED
DEFINER=`restcon`@`64.111.96.0/255.255.224.0` SQL SECURITY DEFINER
VIEW `vHistoricalStoreScores` AS select ...


mysql> SELECT * FROM vHistoricalStoreScores;
+------------------+--------------------+-----------+----------------
+------------------+-------+------+
| compilation | client_location_id | client_id | client_name |
location | month | year |
+------------------+--------------------+-----------+----------------
+------------------+-------+------+
| 4.45833333333333 | 5 | 2 | Client 2 |
Main St. | 1 | 2008 |
| 4.26388888888889 | 1 | 1 | Test Client |
Test Location | 1 | 2008 |
| 4.09027777777778 | 5 | 2 | Client 2 |
Main St. | 12 | 2007 |
+------------------+--------------------+-----------+----------------
+------------------+-------+------+
14 rows in set (0.03 sec)


mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.00 sec)

mysql> USE test;
Database changed

mysql> \. original_database_backup.sql

Query OK, 0 rows affected (0.06 sec)
Query OK, 0 rows affected (0.06 sec)
....
Query OK, 0 rows affected (0.06 sec)
ERROR 1050 (42S01): Table 'vCardQuestionsNested' already exists
ERROR 1050 (42S01): Table 'vClientLocationCodeDates' already exists
ERROR 1050 (42S01): Table 'vClientLocationQuestions' already exists
ERROR 1050 (42S01): Table 'vClientLocationQuestionsNested' already
exists
ERROR 1050 (42S01): Table 'vClientQuestionsNested' already exists
ERROR 1050 (42S01): Table 'vHistoricalStoreScores' already exists
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW CREATE TABLE vHistoricalStoreScores;
+------------------------
+----------------------------------------------
| Table | Create
Table
+------------------------
+----------------------------------------------
| vHistoricalStoreScores | CREATE TABLE `vHistoricalStoreScores` (
`compilation` double default NULL,
`client_location_id` int(10) unsigned default NULL,
`client_id` int(10) unsigned default NULL,
`client_name` varchar(40) default NULL,
`location` varchar(30) default NULL,
`month` bigint(2) default NULL,
`year` bigint(4) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+------------------------
+-----------------------------------------------
1 row in set (0.00 sec)

mysql> SELECT * FROM vHistoricalStoreScores;
Empty set (0.00 sec)

lawpoop@gmail.com wrote:

> mysqldump -uuser -p - --skip-opt --all --complete-insert
> original_database > ./original_database_backup.sql
....

> mysql> \. original_database_backup.sql
>
> Query OK, 0 rows affected (0.06 sec)
> Query OK, 0 rows affected (0.06 sec)
> ...
> Query OK, 0 rows affected (0.06 sec)
> ERROR 1050 (42S01): Table 'vCardQuestionsNested' already exists
> ERROR 1050 (42S01): Table 'vClientLocationCodeDates' already exists
> ERROR 1050 (42S01): Table 'vClientLocationQuestions' already exists
> ERROR 1050 (42S01): Table 'vClientLocationQuestionsNested' already
> exists
> ERROR 1050 (42S01): Table 'vClientQuestionsNested' already exists
> ERROR 1050 (42S01): Table 'vHistoricalStoreScores' already exists


Those error messages look fishy. What version of mysql / mysqldump /
server are you using? Are you superuser when you load the dump?
The above could happen if you don't have the DROP privilege.


Here is how it should look:

~ $mysql test

mysql> create table t1 (c1 int);
Query OK, 0 rows affected (0,08 sec)

mysql> create view v1 as select * from t1;
Query OK, 0 rows affected (0,00 sec)

mysql> Bye

~ $mysqldump --skip-comments --skip-opt --complete-insert test
-- PROLOGUE
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `t1` (
`c1` int(11) default NULL
);
SET character_set_client = @saved_cs_client;

/*!50001 CREATE TABLE `v1` (
`c1` int(11)
) */;
/*!50001 DROP TABLE `v1`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `v1` AS select `t1`.`c1` AS `c1` from `t1` */;
-- EPILOGUE


Can you try what you get for the above example?


XL
--
Axel Schwenke, Support Engineer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/

On Wed, 13 Feb 2008 08:36:56 -0800 (PST), lawpoop@gmail.com wrote:
> OK, here's what I've got:
>
> I have a database with several views defined. I made a dump of this
> database using this mysqldump command:
>

Sorry, I missed that. Could you repeat that command again?

[..]
>
>
> mysql> CREATE DATABASE test;
> Query OK, 1 row affected (0.00 sec)
>
> mysql> USE test;
> Database changed
>
> mysql> \. original_database_backup.sql
>
>
> Query OK, 0 rows affected (0.06 sec)
> Query OK, 0 rows affected (0.06 sec)
> ...
> Query OK, 0 rows affected (0.06 sec)
> ERROR 1050 (42S01): Table 'vCardQuestionsNested' already exists
> ERROR 1050 (42S01): Table 'vClientLocationCodeDates' already exists
> ERROR 1050 (42S01): Table 'vClientLocationQuestions' already exists
> ERROR 1050 (42S01): Table 'vClientLocationQuestionsNested' already
> exists
> ERROR 1050 (42S01): Table 'vClientQuestionsNested' already exists
> ERROR 1050 (42S01): Table 'vHistoricalStoreScores' already exists
> Query OK, 0 rows affected (0.01 sec)

This is really gonna depend on that command. If you specified the
database to save via the --databases option, it's probably switched
right back to the one you saved, and --skip-opt would have also omitted
the DROP TABLE commands right before the CREATE TABLE that error is
complaining about...

--
40. I will be neither chivalrous nor sporting. If I have an unstoppable
superweapon, I will use it as early and as often as possible instead of
keeping it in reserve.
--Peter Anspach's list of things to do as an Evil Overlord

Axel, thanks for taking the time to respond, replies below.

On Feb 13, 11:21 am, Axel Schwenke <axel.schwe...@gmx.de> wrote:
> > ERROR 1050 (42S01): Table 'vCardQuestionsNested' already exists
> > ERROR 1050 (42S01): Table 'vClientLocationCodeDates' already exists
> > ERROR 1050 (42S01): Table 'vClientLocationQuestions' already exists
> > ERROR 1050 (42S01): Table 'vClientLocationQuestionsNested' already
> > exists
> > ERROR 1050 (42S01): Table 'vClientQuestionsNested' already exists
> > ERROR 1050 (42S01): Table 'vHistoricalStoreScores' already exists
>
> Those error messages look fishy. What version of mysql / mysqldump /
> server are you using? Are you superuser when you load the dump?
> The above could happen if you don't have the DROP privilege.

I'm using mysql on a shared webserver. I can ssh into the machine and
use the mysql terminal interface. Here are the versions:

mysql> \s
--------------
mysql Ver 14.12 Distrib 5.0.16, for pc-linux-gnu (i386) using
readline 5.0
....
Server version: 5.0.24a-standard-log
Protocol version: 10
....

$ mysqldump --version
mysqldump Ver 10.10 Distrib 5.0.16, for pc-linux-gnu (i386)

As far as superuser, you mean within mysql? I'm certainly not root on
the host. I do seem to have DROP priviliges for databases and tables:

mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.00 sec)

mysql> USE test;
Database changed

mysql> CREATE TABLE `Clients` (
-> `id` int(10) unsigned NOT NULL auto_increment,
-> `last_mod` timestamp NOT NULL default CURRENT_TIMESTAMP,
-> `client_name` varchar(40) NOT NULL default '',
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (1.75 sec)

mysql> DROP TABLE Clients ;
Query OK, 0 rows affected (0.56 sec)

mysql> DROP DATABASE test;
Query OK, 0 rows affected (0.00 sec)

>
> Here is how it should look:
>
> ~ $mysql test
>
> mysql> create table t1 (c1 int);
> Query OK, 0 rows affected (0,08 sec)
>
> mysql> create view v1 as select * from t1;
> Query OK, 0 rows affected (0,00 sec)
>
> mysql> Bye
>
> ~ $mysqldump --skip-comments --skip-opt --complete-insert test
> -- PROLOGUE
> SET @saved_cs_client = @@character_set_client;
> SET character_set_client = utf8;
> CREATE TABLE `t1` (
> `c1` int(11) default NULL
> );
> SET character_set_client = @saved_cs_client;
>
> /*!50001 CREATE TABLE `v1` (
> `c1` int(11)
> ) */;
> /*!50001 DROP TABLE `v1`*/;
> /*!50001 CREATE ALGORITHM=UNDEFINED */
> /*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
> /*!50001 VIEW `v1` AS select `t1`.`c1` AS `c1` from `t1` */;
> -- EPILOGUE
>
> Can you try what you get for the above example?

It looks like I get basically the same results:

mysql> create database test;
Query OK, 1 row affected (0.01 sec)

mysql> use test;
Database changed

mysql> create table t1 (c1 int);
Query OK, 0 rows affected (0.19 sec)

mysql> create view v1 as select * from t1;
Query OK, 0 rows affected (0.00 sec)

mysql>Bye

[mento]$ mysqldump -p --skip-comments --skip-opt --complete-insert
test
Enter password:
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS,
FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE,
SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE TABLE `t1` (
`c1` int(11) default NULL
);

/*!50001 CREATE TABLE `v1` (
`c1` int(11)
) */;
/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`user`@`ip_address/subnet`
SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`c1` AS `c1` from
`t1`*/;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

So that looks basically like what you got. So far, so good, right?

However, when I get back into the mysql terminal interface and re-run
the dump file, it `v1` gets created as a MyISAM table, but the
subsequent drop and view creation never seem to happen:

mysql> DROP DATABASE test;
Query OK, 2 rows affected (0.00 sec)

mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.01 sec)

mysql> USE test;
Database changed
mysql> \. test.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.04 sec)

Query OK, 0 rows affected (0.04 sec)

ERROR 1050 (42S01): Table 'v1' already exists
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| t1 |
| v1 |
+----------------+
2 rows in set (0.00 sec)

mysql> SHOW CREATE TABLE v1;
+-------+---------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------+
| v1 | CREATE TABLE `v1` (
`c1` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------+
1 row in set (0.00 sec)


Is there some switch or option missing in the mysql config files,
where it tells it to 'read' the "commented statements" ( for lack of a
better term ) ? It just looks like it's not executing the statements
in the 'comments' section. ( I understand that they are technically
not commented; I just don't know what else to call them. ) Again, I'm
on a shared web host without root priviliges, so I can't jack around
with the MySQL setup.

Thanks for your time and effort!

On Feb 13, 11:54 am, "Peter H. Coffin" <hell...@ninehells.com> wrote:
> On Wed, 13 Feb 2008 08:36:56 -0800 (PST), lawp...@gmail.com wrote:
> > OK, here's what I've got:
>
> > I have a database with several views defined. I made a dump of this
> > database using this mysqldump command:
>
> Sorry, I missed that. Could you repeat that command again?

My fault, I left it out

mysqldump -uuser -p - --skip-opt --all --complete-insert
original_database > ./original_database_backup.sql

> > mysql> CREATE DATABASE test;
> > Query OK, 1 row affected (0.00 sec)
>
> > mysql> USE test;
> > Database changed
>
> > mysql> \. original_database_backup.sql
>
> > Query OK, 0 rows affected (0.06 sec)
> > Query OK, 0 rows affected (0.06 sec)
> > ...
> > Query OK, 0 rows affected (0.06 sec)
> > ERROR 1050 (42S01): Table 'vCardQuestionsNested' already exists
> > ERROR 1050 (42S01): Table 'vClientLocationCodeDates' already exists
> > ERROR 1050 (42S01): Table 'vClientLocationQuestions' already exists
> > ERROR 1050 (42S01): Table 'vClientLocationQuestionsNested' already
> > exists
> > ERROR 1050 (42S01): Table 'vClientQuestionsNested' already exists
> > ERROR 1050 (42S01): Table 'vHistoricalStoreScores' already exists
> > Query OK, 0 rows affected (0.01 sec)
>
> This is really gonna depend on that command. If you specified the
> database to save via the --databases option, it's probably switched
> right back to the one you saved, and --skip-opt would have also omitted
> the DROP TABLE commands right before the CREATE TABLE that error is
> complaining about...

It's just one database that I backed up in the file. I did a grep of
the database name in the backup file, and there wasn't any reference.
So I don't think the error was caused by a specific reference to the
name of the originating database, if that's what you are saying.

It can't have skipped back to the original database, because otherwise
the database 'test' would have no tables ( I just created it shortly
before I ran the dump file ), and certainly would have no definition
of any view or table that had the same name of a view in the
originating database. In any case, I would think that any errors it
would have creating views would also be a problem in creating the
tables also. The tables were re-created no problem.

On Feb 13, 11:21 am, Axel Schwenke <axel.schwe...@gmx.de> wrote:

>
> Can you try what you get for the above example?
>

OK, now I think I've got it. How blind am I? If I'm going to use --
skip-opt, I've also got to use --add-drop-table, or else the 'view as
table' doesn't get dropped.


mysqldump--skip-comments --skip-opt --complete-insert --add-drop-table
test
Enter password:
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS,
FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE,
SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
`c1` int(11) default NULL
);

DROP TABLE IF EXISTS `v1`;
/*!50001 DROP VIEW IF EXISTS `v1`*/;
/*!50001 DROP TABLE IF EXISTS `v1`*/;
/*!50001 CREATE TABLE `v1` (
`c1` int(11)
) */;
/*!50001 DROP TABLE IF EXISTS `v1`*/;
/*!50001 DROP VIEW IF EXISTS `v1`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`user`@`ip_address/
subnet_mask` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`c1` AS
`c1` from `t1`*/;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;


Can I save some face by claiming this is still counter-intuitive and
user-unfriendly? Thanks for everyone's !

lawpoop@gmail.com wrote:
>
> I'm using mysql on a shared webserver. I can ssh into the machine and
> use the mysql terminal interface. Here are the versions:
>
> mysql> \s
> --------------
> mysql Ver 14.12 Distrib 5.0.16, for pc-linux-gnu (i386) using
> readline 5.0
> ...
> Server version: 5.0.24a-standard-log
> Protocol version: 10
> ...
>
> $ mysqldump --version
> mysqldump Ver 10.10 Distrib 5.0.16, for pc-linux-gnu (i386)

That's all pretty old stuff and missing some hundred bug fixes.
Especially mysqldump is way too old. The manual page here:
http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html

already lists some gotchas in old versions. Expect to see odd
behaviour of all kind.

> CREATE TABLE `t1` (
> `c1` int(11) default NULL
> );
>
> /*!50001 CREATE TABLE `v1` (
> `c1` int(11)
> ) */;
> /*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`user`@`ip_address/subnet`
> SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`c1` AS `c1` from
> `t1`*/;

The DROP TABLE for the temporary `v1` table is missing.
OK, you found that out yourself

> Can I save some face by claiming this is still counter-intuitive and
> user-unfriendly?

That's why it is fixed in newer versions!


XL
--
Axel Schwenke, Support Engineer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/

On Feb 13, 5:37 pm, Axel Schwenke <axel.schwe...@gmx.de> wrote:

> That's why it is fixed in newer versions!
>

Thanks for being on the ball : )

> XL
> --
> Axel Schwenke, Support Engineer, MySQL AB
>
> Online User Manual:http://dev.mysql.com/doc/refman/5.0/en/
> MySQL User Forums: http://forums.mysql.com/