Archive for the ‘MySQL Workbench’ Category
While I try to keep things simple, sometimes eliminating options and explanations comes back to haunt me. After posting how to open a Fedora firewall port for a LAMP stack, somebody got trapped by my instructions for installing MySQL on Fedora. They got stuck because they had the following setting in their
I’d suggested using that
bind-address value for a DHCP VMware Fedora installation in Step #7. I was trying to create an example for an isolated testing instance, which is why I set the
bind-address to a
localhost.localdomain value. They raised the following error when they tried to connect their base operating system’s version of MySQL Workstation to the Fedora VM:
Failed to Connect to MySQL at 192.168.2.168:3306 with user student
or, this dialog image:
Before you do the next step, please ensure you’re using the right IP address. You can find that by running this command as an authorized sudoer:
ifconfig | grep inet.*netmask.*broadcast
In this case, the command returns:
inet 192.168.2.168 netmask 255.255.255.0 broadcast 192.168.2.255
I’ve since added instructions to the older post to set the
bind-address value in the
my.cnf file as follows when they want to support external connections (naturally that means authorizing port
After you reset the
/etc/my.cnf file, you must
mysqld service. You can do that as the
root user like this:
systemctl restart mysqld
Then, you can test a
student user connection from MySQL Workbench like this:
student user is authorized and the password is correct, you’ll see that the connection now works:
As always, I hope this helps those working through similar issues.
I’ve been setting up a simplified lab environment to let my students learn use in class. This added content will show them how to do reverse engineering with MySQL Workbench.
It’s a complete Fedora image with MySQL and Oracle Database 11g for the course. The uncompressed image is 14GB and the compressed image is 5.3GB. I chose Fedora because it’s the smallest open source image that supports both environments, and Fedora is the closest to Red Hat and Oracle Unbreakable Linux. I’m inclined to make the instance available generally but haven’t figured out the best way to do that.
Here are the new instructions I’m adding and if you have any input leave it as a comment.
You connect as the
student user, which puts you in the
/home/student directory. Once connected to the Fedora OS, you open a Terminal session by clicking on Activities in the upper right hand corner, and then you type
terminal in the search box. When you’re in the Terminal session, use the following command to verify that the
apply_mysql_lab1.sql file is correct:
It should display the following commands:
\. /home/student/Data/cit225/mysql/lib/cleanup.sql \. /home/student/Data/cit225/mysql/lib/create_mysql_store_ri.sql \. /home/student/Data/cit225/mysql/lib/seed_mysql_store_ri.sql
You can run the
apply_mysql_lab1.sql to create the tables in the
studentdb database, and seed them with data. Assuming you’re in the same
/home/student directory, you connect to the
studentdb database with the following syntax:
mysql -ustudent -pstudent studentdb
or, more securely:
mysql -ustudent studentdb -p
Having connected to the
studentdb database, you can run the following command:
It creates a Video store image and seeds it with some basic data. You can use the show command to see the tables you’ve created, like:
+---------------------+ | Tables_in_studentdb | +---------------------+ | address | | common_lookup | | contact | | contacts | | current_rental | | item | | member | | rental | | rental_item | | street_address | | system_user | | telephone | +---------------------+ 12 rows in set (0.00 sec)
After successfully creating and seeding the
studentdb database, you can run MySQL Workbench by launching it from the search field (recommended). Alternatively, you can open it from a terminal session with the following command. Unfortunately, a command-line launch links the terminal and the MySQL Workbench processes and closing the terminal will close the MySQL Workbench.
Here are the instructions for the lab with MySQL Workbench:
- The first displayed page of MySQL Workbench is the home page (click on it or any of the others to see the full size image). Click the ⊕ symbol to the right of the MySQL Workbench title.
- After clicking the the ⊕ symbol, it launches the Startup New Connection dialog. Enter a name for your new connection. I recommend you enter MySQLConnection.
- Click the Test Connection button to see if it works.
- When you click the Test Connection button, MySQL Workbench prompts you for a password. After entering a password and verifying the connection, click the OK button to test the connection.
- Click the OK button to continue.
- Click the gray highlighted MySQLConnection connection icon below the MySQL Connection title to launch the MySQL Workbench application.
- When you click the gray highlighted MySQLConnection connection icon, MySQL Workbench prompts you for a password. After entering a password and verifying the connection, click the OK button to connect to the MySQL Workbench application.
- The MySQL Workbench launches in the default view.
- Click on the Database menu option and then the Reverse Engineering… option, as shown in the illustration.
- This displays the Set Parameters for Connecting to a DBMS dialog. Click the Stored Connection list of values. Choose the MySQLConnection (if you used my suggestion) or the one you created from the list of values of the Stored Connection element. Click the Next button to continue.
- Enter the password and click the OK button to connect to the MySQL database.
- This is an in-progress display, it runs waiting for the password and until the step of the wizard completes.
- This dialog displays when the MySQL Workbench application connects to the database, retrieves a schema (database) list from the database management system, and checks the common server configuration issues. Click the Next button to move to the next step of the wizard.
- This Select Schemas to Reverse Engineer dialog displays any available schemas. You check the schemas that you want. Click the Next button to move to the next step of the wizard.
- Enter the password and click the OK button to connect to the MySQL database to retrieve objects from the database management system.
- This dialog displays when the MySQL Workbench application retrieves objects from the database. Click the Next button to move to the next step of the wizard.
- This dialog displays when the MySQL Workbench application retrieves objects from the schemata and checks the result. Click the Next button to move to the next step of the wizard.
- This dialog displays the tables, views, and routines to import. Click the Execute button to move to the next step of the wizard.
- This dialog displays the tables, views, and routines to import. Click the Execute button to move to the next step of the wizard.
- This dialog displays shows the reverse engineering objects and puts them in the object image. Click the Next button to move to the next step of the wizard.
- This dialog displays a summary of reverse engineering objects. Click the Close button to move to the next step of the wizard.
- The MySQL Model dialog displays after you complete the reverse engineering process. Click the EER Diagram icon or EER Diagram tab to see visual depiction of the database objects.
- The EER Diagram is equivalent to the EER Diagram tab. The Navigator displays the tables as blue rectangles. You can scroll through the Canvas Panel to work with the display of tables and views.
- The next image shows EER Diagram with the tables moved for display purposes. Sometimes there are too many relationship lines, MySQL Workbench lets you split the lines so they don’t clutter the diagram.
- The next image shows you the Property tab of EER Diagram. You should see that the drawSplit checkbox is checked, which suppresses the bottom-most relationship line from displaying in the EER from display on the Canvas Panel.
You can now save the MySQL Workbench file in the Lab 3 directory.
The early release of Fedora 20 disallowed installation of MySQL Workbench but the current version allows it. Almost like Tom Cruise’s Edge of Tomorrow without the drama. All you need to do is follow my earlier instructions for installing MySQL on Fedora 20. I’d check your kernel to know whether it’s supported. You can check that with this command:
<shell> uname -r
My Fedora is at the following version:
Then, you can install MySQL Workbench with
yum, like this:
<shell> sudo yum install mysql-workbench
It generates the following log file, and if you have Oracle 11g XE installed you can ignore the mime-type error:
Loaded plugins: langpacks, refresh-packagekit Resolving Dependencies --> Running transaction check ---> Package mysql-workbench-community.x86_64 0:6.1.7-1.fc20 will be installed --> Processing Dependency: libzip.so.2()(64bit) for package: mysql-workbench-community-6.1.7-1.fc20.x86_64 --> Processing Dependency: libvsqlitepp.so.3()(64bit) for package: mysql-workbench-community-6.1.7-1.fc20.x86_64 --> Processing Dependency: libtinyxml.so.0()(64bit) for package: mysql-workbench-community-6.1.7-1.fc20.x86_64 --> Processing Dependency: liblua-5.1.so()(64bit) for package: mysql-workbench-community-6.1.7-1.fc20.x86_64 --> Processing Dependency: libgtkmm-2.4.so.1()(64bit) for package: mysql-workbench-community-6.1.7-1.fc20.x86_64 --> Processing Dependency: libgdkmm-2.4.so.1()(64bit) for package: mysql-workbench-community-6.1.7-1.fc20.x86_64 --> Processing Dependency: libctemplate.so.2()(64bit) for package: mysql-workbench-community-6.1.7-1.fc20.x86_64 --> Running transaction check ---> Package compat-lua-libs.x86_64 0:5.1.5-1.fc20 will be installed ---> Package ctemplate.x86_64 0:2.2-5.fc20 will be installed ---> Package gtkmm24.x86_64 0:2.24.4-2.fc20 will be installed ---> Package libzip.x86_64 0:0.11.2-1.fc20 will be installed ---> Package tinyxml.x86_64 0:2.6.2-4.fc20 will be installed ---> Package vsqlite++.x86_64 0:0.3.13-3.fc20 will be installed --> Finished Dependency Resolution Dependencies Resolved ================================================================================ Package Arch Version Repository Size ================================================================================ Installing: mysql-workbench-community x86_64 6.1.7-1.fc20 mysql-tools-community 24 M Installing for dependencies: compat-lua-libs x86_64 5.1.5-1.fc20 updates 158 k ctemplate x86_64 2.2-5.fc20 fedora 174 k gtkmm24 x86_64 2.24.4-2.fc20 fedora 748 k libzip x86_64 0.11.2-1.fc20 updates 59 k tinyxml x86_64 2.6.2-4.fc20 updates 49 k vsqlite++ x86_64 0.3.13-3.fc20 updates 58 k Transaction Summary ================================================================================ Install 1 Package (+6 Dependent packages) Total download size: 26 M Installed size: 119 M Is this ok [y/d/N]: y Downloading packages: (1/7): compat-lua-libs-5.1.5-1.fc20.x86_64.rpm | 158 kB 00:01 (2/7): ctemplate-2.2-5.fc20.x86_64.rpm | 174 kB 00:01 (3/7): tinyxml-2.6.2-4.fc20.x86_64.rpm | 49 kB 00:00 (4/7): gtkmm24-2.24.4-2.fc20.x86_64.rpm | 748 kB 00:01 (5/7): vsqlite++-0.3.13-3.fc20.x86_64.rpm | 58 kB 00:00 (6/7): libzip-0.11.2-1.fc20.x86_64.rpm | 59 kB 00:02 (7/7): mysql-workbench-community-6.1.7-1.fc20.x86_64.rpm | 24 MB 00:08 -------------------------------------------------------------------------------- Total 2.9 MB/s | 26 MB 00:08 Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : gtkmm24-2.24.4-2.fc20.x86_64 1/7 Installing : libzip-0.11.2-1.fc20.x86_64 2/7 Installing : vsqlite++-0.3.13-3.fc20.x86_64 3/7 Installing : ctemplate-2.2-5.fc20.x86_64 4/7 Installing : compat-lua-libs-5.1.5-1.fc20.x86_64 5/7 Installing : tinyxml-2.6.2-4.fc20.x86_64 6/7 Installing : mysql-workbench-community-6.1.7-1.fc20.x86_64 7/7 Error in file "/usr/share/applications/oraclexe-startdb.desktop": "Application/database" is an invalid MIME type ("Application" is an unregistered media type) Verifying : tinyxml-2.6.2-4.fc20.x86_64 1/7 Verifying : compat-lua-libs-5.1.5-1.fc20.x86_64 2/7 Verifying : ctemplate-2.2-5.fc20.x86_64 3/7 Verifying : vsqlite++-0.3.13-3.fc20.x86_64 4/7 Verifying : mysql-workbench-community-6.1.7-1.fc20.x86_64 5/7 Verifying : libzip-0.11.2-1.fc20.x86_64 6/7 Verifying : gtkmm24-2.24.4-2.fc20.x86_64 7/7 Installed: mysql-workbench-community.x86_64 0:6.1.7-1.fc20 Dependency Installed: compat-lua-libs.x86_64 0:5.1.5-1.fc20 ctemplate.x86_64 0:2.2-5.fc20 gtkmm24.x86_64 0:2.24.4-2.fc20 libzip.x86_64 0:0.11.2-1.fc20 tinyxml.x86_64 0:2.6.2-4.fc20 vsqlite++.x86_64 0:0.3.13-3.fc20 Complete!
After successfully installing MySQL Workbench, you can launch it with the following command:
It should launch the following MySQL Workbench home page (click on it to see the full size image):
Somebody asked me if there was a cheaper alternative to using the Embarcadero Data Architect (a data modeling tool). I said sure, you can use the MySQL Workbench. My friend laughed and said, it’s to model Oracle databases and they use different data types. I broke the news to him that he can create his own user defined types and use MySQL Workbench to model problems for the Oracle Database 11g.
For example, you can launch the MySQL Workbench, and click on the Model menu option, and in the menu window click on the User Defined Types choice, as shown in the following:
Choosing the User Defined Type option, launches the following form. You can enter customized user defined types in the User Defined Types module:
You enter user defined types by entering a name value and choosing valid MySQL type value before clicking the Add button. When you’ve added your last user defined type, click the OK button instead of the Add button. The next screen shot shows how you can create Oracle Database 11g native data types, specifically the
VARCHAR2 data types.
Hopefully, this has shown that you can create User Defined Types let you use MySQL Workbench to create Oracle ERD models. Here’s an example of a table with Oracle’s
VARCHAR2 data types:
Yes, MySQL Workbench is a marvelous tool with wide potential for use to solve problems with MySQL and other databases.
One of my students asked how to convert MySQL Workbench’s default Crow’s Foot (IE) diagram to one of the other supported formats – Classic, Connect to Columns, UML, and IDEF1X. Crow’s Foot is also known as the Information Engineering Model method (covered in Chapter 3 of my MySQL Workbench: Data Modeling & Development.
It quite simple, you open the Model Overview window, click on the Model menu choice. In the dialog, click on the Relationship Notation menu option. Click on one of the choices in the nested menu, like Column to Columns.
Hope this helps those working with MySQL Workbench.
The LinkedIn MySQL DB Development group posed a questions on how to handle images. Naturally, the argument always goes: Should images be deployed in the database or the file system? I believe they should be stored in the database because the cost and time associated is too high with regard to managing files, a file naming schema, and backing up the file system discretely from the database.
Since there’s a significant difference between the backup of transactional data and image data, they should be placed in different databases. The
imagedb database is where you would place the images and large text descriptions, as shown in the MySQL Workbench ERD:
The imagedb ERD splits the foreign key references back to the
system_user table, which contains the individual user credentials. The
system_user table serves as the Access Control List (ACL) for the application.
Until I get a chance to write the code for this model, you can refer to the generic PHP/MySQL solution from several years back (its code source was last tested with PHP 5.3). As always, I hope this helps.
Since MySQL Workbench 6.0 isn’t available for Fedora, Version 20, I’m having my students install it on their local Windows and Mac OS X operating systems. You can configure the
/etc/sysconfig/iptables file to enable port 3306 after installing MySQL on Fedora.
You can open a port by using the
firewall-config utility (easy way) or by adding the following line to the
/etc/sysconfig/iptables file (Fedora’s instructions on editing
iptables [hard way]). The file won’t exist initially, but you can create it by running the following command as the
root superuser or sudoer:
shell> service iptables save
You you can run the following commands as the
root superuser, which saves the line in the
shell> iptables -A INPUT -m conntrack --ctstate NEW -m tcp -p tcp --dport 3306 -j ACCEPT shell> iptables-save
After making the change to the
/etc/iptables file you can change the firewall by running the following command as the
shell> service iptables restart
Just make sure you don’t inadvertently start both iptables and ip6tables as services. You can check that only one is running by using the following commands:
shell> service iptables status shell> service ip6tables status
MySQL Workbench Configuration
- The first thing you need to do is click on the
+symbol in the circle to the right of the MySQL Connections text label. It launches the Setup New Connection dialog.
- The second thing you need to do is enter a Connection Name, Hostname, Port, and Username. Then, click the Test Connection button.
- The Test Connection button launches the Connect to MySQL Server dialog. Enter the password for the
studentuser (or whatever user you’re interested in), and then click the OK button.
- When the credentials in the Connect to MySQL Server dialog work, you see the following confirmation dialog message. Click the OK button to continue and you’ll see a new VMWare Fedora Instance connection icon.
- Click the VMWare Fedora Instance connection to start a new connection.
- The VMWare Fedora Instance button launches the Connect to MySQL Server dialog. Like you did when configuring the connection, enter the password for the
studentuser (or whatever user you’re interested in), and then click the OK button. It launches an interactive panel that lets you run, edit, or save the SQL script file.
- Type the following two lines in the Query1 panel (at least if you have a
USE studentdb; SELECT DATABASE();
I registered yesterday for Oracle OpenWorld 2013, and I’ll look forward to seeing friends there. Having worked in the Oracle 12c beta for a year, I’ll be interested in the presentations. Also, hearing more about Java 7 at JavaOne. On the downside, I’m missing MySQL Connect this year.
Cloud computing offers many possibilities, and container and pluggable databases are a great solution. We’ve two new acronyms with the Oracle 12c release. A containerized database is a CDB, and a pluggable database is a PDB. I’m looking forward to seeing more about the provisioning of PDBs during the conference. If you’re new to the changes, check out CDBs and PDBs in Chapter 17 in the Oracle 12c Concepts manual.
A couple of my favorite new features are Identity and Invisible Columns. If you’re unfamiliar with the new features for application development, let me recommend this Oracle White Paper. Also, for reference I’ve covered identity and invisible columns thoroughly in the Oracle Database 12c PL/SQL Programming book, which will be available in December.
Missing the MySQL Connect 2013 Bus
Unfortunately, travel budgets preclude me attending MySQL Connect 2013 this year (alas, I’ll miss the bus). It was hard because I’d like to see what’s up with MySQL (since I was a closet MySQL user at Oracle before they acquired it). Anyway, if you’re there, make sure you check out MySQL Workbench 6 for me. Also, I’d like to thank Dave Stokes for the AWESOME review he wrote on Amazon.com for my MySQL Workbench: Data Modeling & Development book. Maybe, I’ll get to go to MySQL Connect 2014 next year.
My install instructions on the web site were old, somebody wanted me to publish another set of screen capture for the MySQL 5.6 install and configuration. This is it for Windows 7 using the downloadable MSI file.
The installation from MySQL’s perspective is actually the installation and configuration of MySQL. For your convenience and reference, I’ve already installed the pre-requisites for MySQL. They’re:
- Visual Studio Tools for Office 20120 Runtime
- Microsoft .NET Framework 4 Client Profile
- Microsoft Excel 2007 or greater
- Microsoft .NET Framework 4 Client Profile
- Microsoft Visual C++ 2010 32-bit runtime
- Microsoft .NET Framework 4 Client Profile
Below are the installation steps after you download the current release
.msi file. The icon should look like the one to the right. For this example, I”m using the
mysql-installer-community-18.104.22.168.msi. Double-click the icon on your desktop or from your
C:\Users\username\Downloads folder. While working through the steps, you can launch any of the small images to the left if you’d like to see what your screen should look like (generally with a right click to open in a new window).
- The first screen is a Windows 7 dialog box. Click the Run button to install launch the MySQL 5.6 Installer.
- The second screen is a Windows 7 dialog box. It advises you that the MySQL Installer is working and lets you cancel that operation. Don’t click the Cancel button unless you want to stop the MySQL 5.6 Installer.
- The third screen is a MySQL Installer message box. It closes when ready to proceed. Ignore it, unless it’s there for more than a couple minutes. If that occurs you’ve got something wrong with your Windows installation or a very slow computer. If the former, kill the installation process; if the latter, wait patiently.
- The fourth screen in the process is the first MySQL Installer screen. Here you choose what you want to do. You can install MySQL products, inquire about MySQL, or check physical resource components. Provided you installed the prerequisites listed above, you should be prepared to install MySQL Products. Click the Install MySQL Products link to proceed or one of the others to explore.
- The second MySQL Installer screen is the license form. You must check the I accept the license terms checkbox to enable the Next button. Once the Next button is enabled, click it to proceed.
- The third MySQL Installer screen connects to the Internet and finds the latest product update. You can check the Skip the check for updates (not recommended) checkbox to skip this but for the example we’ll check anyway. Click the Execute button to proceed.
- The fourth MySQL Installer screen connects to the Internet and finds the latest product update. You can check the Skip the check for updates (not recommended) checkbox to skip this but for the example we’ll check anyway. Click the Execute button to proceed.
- The fifth MySQL Installer screen acknowledges the latest update is what you’re installing. Click the Next button to proceed.
The sixth MySQL Installer screen gives you five choices for the installation, which are listed below. You most likely want to install the Developer Default, so click the Developer Default radio button and then, click the Next button to proceed.
- MySQL Server
- Both the client and server software for the MySQL Server
- MySQL Workbench
- The GUI application to develop for and manage the server.
- MySQL Visual Studio Plugin
- To work with the MySQL Server from VS.
- MySQL Connectors
- Connector/Net, Java, C/C++, OBDC and others.
- Examples and tutorials
- To help you get started with your development.
- Allows you to read the documentation offline.
- The seventh MySQL Installer screen performs a system check for the pre-requisites, which I listed before the installation. Assuming you installed them, you should see a screen that confirms your system configuration is ready for installation. Click the Next button to proceed.
The eighth MySQL Installer screen performs displays the products that it’ll install, which are listed below and available in the full image to the left. Click the Execute button to install the products.
- MySQL Server 5.6.11
- MySQL Workbench CE 5.2.47
- MySQL Notifier 1.0.3
- MySQL for Excel 1.1.1
- Connector/ODBC 5.2.4
- Connector/C++ 1.1.2
- Connector/C++ 1.1.2
- Connector/J 5.1.24
- Connector/NET 6.6.5
- MySQL Documentation 5.6.11
- Samples and Examples 5.6.11
- The nineth MySQL Installer screen shows the installation by product and it can take a couple minutes. The screen to the left displays progress more than halfway complete. You don’t need to do anything in this step until all products are complete.
- The tenth MySQL Installer screen shows the completed installation. Everything should install successfully, as shown in the image. Click the Next button to proceed.
This concludes the installation of the MySQL products. The next section shows you how to configure MySQL.
You have two basic options, the simple one and the advanced one. These steps will show you how to perform an advanced configuration. I’ve opted to maintain the step numbering from the beginning of the installation. Here are the steps:
- The eleventh MySQL Installer screen is the first MySQL Configuration screen. You can click the Show Details button or begin the configuration. Click the Next button to proceed.
- The second MySQL Configuration screen sets the server configuration type, enables TCP/IP networking (as opposed to a socket model), and lets you enable Advanced Configuration. For this installation, we enable the Show Advanced Options checkbox before you click the Next button.
The third MySQL Configuration screen sets the password and lets you create MySQL User Accounts. It’s much easier to let the install proceed and use MySQL Workbench to create databases, users, and roles; plus grant permissions through the GUI environment. Enter the
rootpassword twice, a trivial and unsecure password
cangetinis what I recommend to my students who won’t have any meaningful information in the database. Make sure you can remember the password you enter. Clearly, a better password is required for real environments. After entering the password twice, click the Next button to proceed.
The fourth MySQL Configuration screen sets the Windows Service Name, and you should probably make sure there isn’t another MySQL56 service on the machine before you proceed. You have the choice of running the Windows Service using the Standard System Account or a Customer User account. Unless you’re an expert at Windows 7 administration, you should probably choose the Standard System Account as the one running the Windows 7 service. Click the Next button.
The fifth MySQL Configuration screen sets the logging options. You only need Show Query Log typically, but the Error Log is helpful. Make the choices and click the Next button.
- The sixth MySQL Configuration screen explains the next step. It installs the sample files and example databases. You can see what you’ve installed when you click the Show Details button, which is what I did to get the image at the left. The default choice installs the samples and example databases, which can’t hurt. You’ll need the test database if you install DBD::mysql for Perl. If you don’t want them, you can drop them from the database.
The seventh MySQL Configuration screen explains you’ve completed configuring the MySQL Server. Click the Next button to proceed.
The eighth MySQL Configuration screen explains you’ve finished everything. You can copy the log file to clipboard, which allows you to see everything that was done. Click the Finish button to complete the installation and configuration.
Just one caveat (that’s a warning), this installation doesn’t put the MySQL executable into your System
%PATH% variable. You’ll need to do that, and if I get a chance I’ll put a post together for that. I know one or two of my students may need it later.
I hope this helps those you are using the new installer for the first time. It’s a superior tool to the old one, which was also a good tool.
Somebody ran into a problem after reading about the MySQL
CREATE statement and the
AUTO_INCREMENT option. They couldn’t get a
CREATE statement to work with an
AUTO_INCREMENT value other than the default of 1. The problem was they were using this incorrect syntax:
CREATE TABLE elvira ( elvira_id int unsigned PRIMARY KEY AUTO_INCREMENT=1001 , movie_title varchar(60)) ENGINE=InnoDB CHARSET=utf8;
It raises this error:
ERROR 1064 (42000): 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 '=1001 , movie_title varchar(60)) ENGINE=InnoDB CHARSET=utf8' at line 2
They concluded that MySQL requires you to assign a default value of 1 as the initial automatic numbering value; then you use the
ALTER statement to change the initial sequence value. That assumption is incorrect. The problem was with their assignment of an overriding
AUTO_INCREMENT value inside the parenthetical list of columns. That assignment needs to occur after the list of columns and constraints, like
CREATE TABLE elvira ( elvira_id int unsigned PRIMARY KEY AUTO_INCREMENT , movie_title varchar(60)) ENGINE=InnoDB AUTO_INCREMENT=1001 CHARSET=utf8;
It’s not an unlikely mistake since there’s no clear example on either of the referenced web pages (at the time of writing). It would be nice if they were added but I’m of the opinion some of the reference manual pages are too sparse.
After creating the table, you have the generic fix that seems to appear most often as an answer to setting or re-setting the auto numbering sequence of a MySQL table:
ALTER TABLE elvira AUTO_INCREMENT=1001;
Why would you set the auto numbering sequence to something other than 1? Some designers consider it a best practice to increment from a set point like 101 or 1,001 for your Application Programming Interface (API) and they leave a readily identifiable sequence number set for pre- or post-seeded data in tables. The region of preallocated numbers are typically only used in a few of the tables, in any application, but consistently managing sequences across all tables does sometimes simplifies data diagnostics.
Hope this helps somebody looking for a syntax fix. By the way, you can find it on Page 162 of the Oracle Database 11g & MySQL 5.6 Developer Handbook.