title: ProgrammingError: 1064 while executing inspectdb with django and mysql database
body: <p>I have two databases configured in settings.py:</p>
  
  <pre><code>DATABASES = {
      'default': {
          'ENGINE': 'django.db.backends.postgresql',
          'NAME': 'postgres',
          'USER': 'postgres',
          'PASSWORD': 'password',
          'HOST': '127.0.0.1',
          'PORT': '5432',
      },
      'db2': {
          'ENGINE': 'django.db.backends.mysql',
          'NAME': 'database',
          'USER': 'user',
          'PASSWORD': 'password',
          'HOST': 'ip address',
          'PORT': 'port',
      }
  }
  </code></pre>
  
  <p>The server which hosts db2 uses mysql 4.1.2. I am using Python 3.7.3 + django 2.2.5 + mysqlclient 1.4.4.</p>
  
  <p>When I run <code>python manage.py inspectdb --database db2</code> I get the following error:</p>
  
  <blockquote>
    <p>django.db.utils.ProgrammingError: (1064, "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 'TABLES' at line 1")</p>
  </blockquote>
  
  <p>I have printed the 'query' and it is: <code>SHOW FULL TABLES</code></p>
  
  <p>I have tried to manually connect to the database (MySQLdb.connect + cursor) and retrieve data from a table and it worked fine.</p>
  
  <p>My problem is basically the same as (<a href="https://stackoverflow.com/questions/35413680/error-1064-django-inspectdb">Error 1064 Django inspectdb</a>), but that guy didn't get any help. Hopefully I will.</p>
  
  <p>What I am trying to accomplish is use data from db2 as foreign keys in the default. For example, I have some items described in the default database and some persons defined in db2. I would like to associate a person from db2 to an item from default. In order to do this, I thought that building a model with inspectdb would help.</p>
  
  <p>The full output is:</p>
  
  <pre><code># This is an auto-generated Django model module.
  # You'll have to do the following manually to clean this up:
  #   * Rearrange models' order
  #   * Make sure each model has one field with primary_key=True
  #   * Make sure each ForeignKey has `on_delete` set to the desired behavior.
  #   * Remove `managed = False` lines if you wish to allow Django to create, modify, and delete the table
  # Feel free to rename the models, but don't rename db_table values or field names.
  from django.db import models
  Traceback (most recent call last):
    File "C:\Users\nxf52820\OneDrive - NXP\Aplicatii\PyCharm\AMPLab\AMPLabEnv\lib\site-packages\django\db\backends\utils.py", line 82, in _execute
      return self.cursor.execute(sql)
    File "C:\Users\nxf52820\OneDrive - NXP\Aplicatii\PyCharm\AMPLab\AMPLabEnv\lib\site-packages\django\db\backends\mysql\base.py", line 71, in execute
      return self.cursor.execute(query, args)
    File "C:\Users\nxf52820\OneDrive - NXP\Aplicatii\PyCharm\AMPLab\AMPLabEnv\lib\site-packages\MySQLdb\cursors.py", line 209, in execute
      res = self._query(query)
    File "C:\Users\nxf52820\OneDrive - NXP\Aplicatii\PyCharm\AMPLab\AMPLabEnv\lib\site-packages\MySQLdb\cursors.py", line 315, in _query
      db.query(q)
    File "C:\Users\nxf52820\OneDrive - NXP\Aplicatii\PyCharm\AMPLab\AMPLabEnv\lib\site-packages\MySQLdb\connections.py", line 226, in query
      _mysql.connection.query(self, query)
  MySQLdb._exceptions.ProgrammingError: (1064, "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 'TABLES' at line 1")
  
  The above exception was the direct cause of the following exception:
  
  Traceback (most recent call last):
    File "manage.py", line 21, in &lt;module&gt;
      main()
    File "manage.py", line 17, in main
      execute_from_command_line(sys.argv)
    File "C:\Users\nxf52820\OneDrive - NXP\Aplicatii\PyCharm\AMPLab\AMPLabEnv\lib\site-packages\django\core\management\__init__.py", line 381, in execute_from_command_line
      utility.execute()
    File "C:\Users\nxf52820\OneDrive - NXP\Aplicatii\PyCharm\AMPLab\AMPLabEnv\lib\site-packages\django\core\management\__init__.py", line 375, in execute
      self.fetch_command(subcommand).run_from_argv(self.argv)
    File "C:\Users\nxf52820\OneDrive - NXP\Aplicatii\PyCharm\AMPLab\AMPLabEnv\lib\site-packages\django\core\management\base.py", line 323, in run_from_argv
      self.execute(*args, **cmd_options)
    File "C:\Users\nxf52820\OneDrive - NXP\Aplicatii\PyCharm\AMPLab\AMPLabEnv\lib\site-packages\django\core\management\base.py", line 364, in execute
      output = self.handle(*args, **options)
    File "C:\Users\nxf52820\OneDrive - NXP\Aplicatii\PyCharm\AMPLab\AMPLabEnv\lib\site-packages\django\core\management\commands\inspectdb.py", line 34, in handle
      for line in self.handle_inspection(options):
    File "C:\Users\nxf52820\OneDrive - NXP\Aplicatii\PyCharm\AMPLab\AMPLabEnv\lib\site-packages\django\core\management\commands\inspectdb.py", line 60, in handle_inspection
      table_info = connection.introspection.get_table_list(cursor)
    File "C:\Users\nxf52820\OneDrive - NXP\Aplicatii\PyCharm\AMPLab\AMPLabEnv\lib\site-packages\django\db\backends\mysql\introspection.py", line 55, in get_table_list
      cursor.execute("SHOW FULL TABLES")
    File "C:\Users\nxf52820\OneDrive - NXP\Aplicatii\PyCharm\AMPLab\AMPLabEnv\lib\site-packages\django\db\backends\utils.py", line 99, in execute
      return super().execute(sql, params)
    File "C:\Users\nxf52820\OneDrive - NXP\Aplicatii\PyCharm\AMPLab\AMPLabEnv\lib\site-packages\django\db\backends\utils.py", line 67, in execute
      return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
    File "C:\Users\nxf52820\OneDrive - NXP\Aplicatii\PyCharm\AMPLab\AMPLabEnv\lib\site-packages\django\db\backends\utils.py", line 76, in _execute_with_wrappers
      return executor(sql, params, many, context)
    File "C:\Users\nxf52820\OneDrive - NXP\Aplicatii\PyCharm\AMPLab\AMPLabEnv\lib\site-packages\django\db\backends\utils.py", line 84, in _execute
      return self.cursor.execute(sql, params)
    File "C:\Users\nxf52820\OneDrive - NXP\Aplicatii\PyCharm\AMPLab\AMPLabEnv\lib\site-packages\django\db\utils.py", line 89, in __exit__
      raise dj_exc_value.with_traceback(traceback) from exc_value
    File "C:\Users\nxf52820\OneDrive - NXP\Aplicatii\PyCharm\AMPLab\AMPLabEnv\lib\site-packages\django\db\backends\utils.py", line 82, in _execute
      return self.cursor.execute(sql)
    File "C:\Users\nxf52820\OneDrive - NXP\Aplicatii\PyCharm\AMPLab\AMPLabEnv\lib\site-packages\django\db\backends\mysql\base.py", line 71, in execute
      return self.cursor.execute(query, args)
    File "C:\Users\nxf52820\OneDrive - NXP\Aplicatii\PyCharm\AMPLab\AMPLabEnv\lib\site-packages\MySQLdb\cursors.py", line 209, in execute
      res = self._query(query)
    File "C:\Users\nxf52820\OneDrive - NXP\Aplicatii\PyCharm\AMPLab\AMPLabEnv\lib\site-packages\MySQLdb\cursors.py", line 315, in _query
      db.query(q)
    File "C:\Users\nxf52820\OneDrive - NXP\Aplicatii\PyCharm\AMPLab\AMPLabEnv\lib\site-packages\MySQLdb\connections.py", line 226, in query
      _mysql.connection.query(self, query)
  django.db.utils.ProgrammingError: (1064, "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 'TABLES' at line 1")
  </code></pre>
  
  <p>EDIT: I have identified the function which send the query:</p>
  
  <pre><code>    def get_table_list(self, cursor):
          """Return a list of table and view names in the current database."""
          cursor.execute("SHOW FULL TABLES")
          return [TableInfo(row[0], {'BASE TABLE': 't', 'VIEW': 'v'}.get(row[1]))
                  for row in cursor.fetchall()]
  </code></pre>
  
  <p>This is further used in:</p>
  
  <pre><code>    def handle_inspection(self, options):
          connection = connections[options['database']]
          # 'table_name_filter' is a stealth option
          table_name_filter = options.get('table_name_filter')
  
          def table2model(table_name):
              return re.sub(r'[^a-zA-Z0-9]', '', table_name.title())
  
          with connection.cursor() as cursor:
              yield "# This is an auto-generated Django model module."
              yield "# You'll have to do the following manually to clean this up:"
              yield "#   * Rearrange models' order"
              yield "#   * Make sure each model has one field with primary_key=True"
              yield "#   * Make sure each ForeignKey has `on_delete` set to the desired behavior."
              yield (
                  "#   * Remove `managed = False` lines if you wish to allow "
                  "Django to create, modify, and delete the table"
              )
              yield "# Feel free to rename the models, but don't rename db_table values or field names."
              yield 'from %s import models' % self.db_module
              known_models = []
              table_info = connection.introspection.get_table_list(cursor)
  
              # Determine types of tables and/or views to be introspected.
              types = {'t'}
              if options['include_partitions']:
                  types.add('p')
              if options['include_views']:
                  types.add('v')
  
              for table_name in (options['table'] or sorted(info.name for info in table_info if info.type in types)):
                  if table_name_filter is not None and callable(table_name_filter):
                      if not table_name_filter(table_name):
                          continue
                  try:
                      try:
                          relations = connection.introspection.get_relations(cursor, table_name)
                      except NotImplementedError:
                          relations = {}
                      try:
                          constraints = connection.introspection.get_constraints(cursor, table_name)
                      except NotImplementedError:
                          constraints = {}
                      primary_key_column = connection.introspection.get_primary_key_column(cursor, table_name)
                      unique_columns = [
                          c['columns'][0] for c in constraints.values()
                          if c['unique'] and len(c['columns']) == 1
                      ]
                      table_description = connection.introspection.get_table_description(cursor, table_name)
                  except Exception as e:
                      yield "# Unable to inspect table '%s'" % table_name
                      yield "# The error was: %s" % e
                      continue
  
                  yield ''
                  yield ''
                  yield 'class %s(models.Model):' % table2model(table_name)
                  known_models.append(table2model(table_name))
                  used_column_names = []  # Holds column names used in the table so far
                  column_to_field_name = {}  # Maps column names to names of model fields
                  for row in table_description:
                      comment_notes = []  # Holds Field notes, to be displayed in a Python comment.
                      extra_params = OrderedDict()  # Holds Field parameters such as 'db_column'.
                      column_name = row.name
                      is_relation = column_name in relations
  
                      att_name, params, notes = self.normalize_col_name(
                          column_name, used_column_names, is_relation)
                      extra_params.update(params)
                      comment_notes.extend(notes)
  
                      used_column_names.append(att_name)
                      column_to_field_name[column_name] = att_name
  
                      # Add primary_key and unique, if necessary.
                      if column_name == primary_key_column:
                          extra_params['primary_key'] = True
                      elif column_name in unique_columns:
                          extra_params['unique'] = True
  
                      if is_relation:
                          rel_to = (
                              "self" if relations[column_name][1] == table_name
                              else table2model(relations[column_name][1])
                          )
                          if rel_to in known_models:
                              field_type = 'ForeignKey(%s' % rel_to
                          else:
                              field_type = "ForeignKey('%s'" % rel_to
                      else:
                          # Calling `get_field_type` to get the field type string and any
                          # additional parameters and notes.
                          field_type, field_params, field_notes = self.get_field_type(connection, table_name, row)
                          extra_params.update(field_params)
                          comment_notes.extend(field_notes)
  
                          field_type += '('
  
                      # Don't output 'id = meta.AutoField(primary_key=True)', because
                      # that's assumed if it doesn't exist.
                      if att_name == 'id' and extra_params == {'primary_key': True}:
                          if field_type == 'AutoField(':
                              continue
                          elif field_type == 'IntegerField(' and not connection.features.can_introspect_autofield:
                              comment_notes.append('AutoField?')
  
                      # Add 'null' and 'blank', if the 'null_ok' flag was present in the
                      # table description.
                      if row.null_ok:  # If it's NULL...
                          extra_params['blank'] = True
                          extra_params['null'] = True
  
                      field_desc = '%s = %s%s' % (
                          att_name,
                          # Custom fields will have a dotted path
                          '' if '.' in field_type else 'models.',
                          field_type,
                      )
                      if field_type.startswith('ForeignKey('):
                          field_desc += ', models.DO_NOTHING'
  
                      if extra_params:
                          if not field_desc.endswith('('):
                              field_desc += ', '
                          field_desc += ', '.join('%s=%r' % (k, v) for k, v in extra_params.items())
                      field_desc += ')'
                      if comment_notes:
                          field_desc += '  # ' + ' '.join(comment_notes)
                      yield '    %s' % field_desc
                  is_view = any(info.name == table_name and info.type == 'v' for info in table_info)
                  is_partition = any(info.name == table_name and info.type == 'p' for info in table_info)
                  for meta_line in self.get_meta(table_name, constraints, column_to_field_name, is_view, is_partition):
                      yield meta_line
  </code></pre>
  
  <p>If I replace <code>SHOW FULL TABLES</code> with <code>SHOW TABLES</code>, I would also need to change the return of the function. Any ideas if I could do this in a way that would be equivalent with what the function currently returns, but without the VIEW part?</p>