Export as CSV - No database selected #16988

nunoperalta · 2021-07-03T21:34:56Z

Describe the bug

I'm getting the problem described in #16668 right now...
I'm definitely with a database (and table) selected, I ran a SELECT query there, and I'm using the "Export" function below the results. Then I select "CSV" as the option, and the "export.csv" file has an HTML output with the query SQL there, and an error at the end saying:

#1046 - No database selected

To Reproduce

Steps to reproduce the behavior:

Go to a database, then table
Open the SQL tab, type a SQL, get results
Click "Export" below the results
Export as CSV

(I edited the contents of the image)

Server configuration

Operating system: Docker phpMyAdmin
Database version: MariaDB 10.5.11
PHP version: 7.4.20
phpMyAdmin version: 5.1.1

Client configuration

Browser: Chrome
Operating system: Windows 10

I also tried to put "USE mydb;" at the beginning of the query, but that line seems to be removed on the Export.

williamdes · 2021-07-04T13:43:11Z

Hi @nunoperalta
Could you export the test database and paste the query here please ?

nunoperalta · 2021-07-04T14:21:42Z

Hey. This is a production database. I edited the screenshot above to remove private information.
I tried to explain a bit how the SELECT query looked like.
Do you believe what can be causing this is due to Data, the Table Structure, or the SQL Query? What do you think is the most suspicious?

taulantxhakli · 2021-07-04T19:29:21Z

I read somewhere that you need to make sure there is a name for your database, or else it will be confused. Now, seeing as you stated that you have contents in the database already (not showing it due to privacy), that seems to be in order.
See if it works by using this query: USE your_db_name;.
Make sure also to not have characters in the name, such as % or &. I had an issue before where the query was getting confused on whether I am using that as a name or not.

williamdes · 2021-07-04T20:15:27Z

I had an issue before where the query was getting confused on whether I am using that as a name or not.

phpMyAdmin should not prevent you from using database names with un-usual characters, if you have examples let us know ;)

SQL Query

I would opt for this one first, we have some logic that sometimes transforms queries

If anyone can confirm reproducting on demo servers (root, no password) it will help me while having a look to this issue this week

taulantxhakli · 2021-07-04T20:31:25Z

There was one instance of this where I had the name %sprun for one of my databases. When I wanted to change the name from that to %spdrun, it gave me an error. When looking at the error log every time I tried, it would continuously create % symbols to the DB's name after every attempt. Adding to that, when I used this DB name for a website, the query would not work and it would give me an error. < - I don't have the old logs for this so I cannot remember what it said, but it pertained to me having the %. Removing that from the DB name made everything work seamlessly.

williamdes · 2021-07-04T21:18:48Z

There was one instance of this where I had the name %sprun for one of my databases. When I wanted to change the name from that to %spdrun, it gave me an error. When looking at the error log every time I tried, it would continuously create % symbols to the DB's name after every attempt. Adding to that, when I used this DB name for a website, the query would not work and it would give me an error. < - I don't have the old logs for this so I cannot remember what it said, but it pertained to me having the %. Removing that from the DB name made everything work seamlessly.

This is indeed very interesting, %s or %d or anything in the sprintf spec could cause issues
I will try to setup some data and find the places where it fails
See: https://www.php.net/manual/function.sprintf.php

nunoperalta · 2021-07-04T22:51:50Z

See if it works by using this query: USE your_db_name;.

I said above I did try putting that at the beginning of the query, but that line seems to be removed on the Export.

Make sure also to not have characters in the name, such as % or &.

My database name is "db_something". Only "_" is special character.

SQL Query

Ok, so the SQL query is like this:

select * from (
SELECT t.tid,
case when main.level = 3 then main.parent_fid else main.fid end as new_fid_level2,
case when main.level = 3 then parent.parent_fid else main.parent_fid end as new_fid_level1
FROM `some_table` t
straight_JOIN some_othertable main using (fid)
LEFT JOIN some_othertable parent on main.parent_fid = parent.fid
) t
where new_fid_level2 is not null;

(only changed the table names - everything else is untouched)

duronrulez · 2021-09-08T06:58:32Z

can confirm we're experiencing the same type of issue (no database selected error) when exporting complex query results.

duronrulez · 2021-09-08T07:31:02Z

here is an reproducable example (on the 5.1.1 demo PMA works the same way):

structure and data:

create database test_16988;
use test_16988;

CREATE TABLE t1 (
    id INT PRIMARY KEY
);

CREATE TABLE t2 (
    id INT PRIMARY KEY
);

INSERT INTO t1 VALUES (1),(2),(3);
INSERT INTO t2 VALUES (2),(3),(4);

Query to try and export the results of:

SELECT * FROM (SELECT id FROM t1 UNION ALL SELECT id FROM t2 ) as results;

Expected result: a csv file, actual result:

<div class="alert alert-danger" role="alert"><h1>Error</h1><p><strong>SQL query:</strong>  <a href="#" class="copyQueryBtn" data-text="SELECT * FROM
        (SELECT id
FROM t1
UNION ALL
SELECT id
FROM t2
) as results">Copy</a>
<a href="./url.php?url=https%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F8.0%2Fen%2Fselect.html" target="mysql_doc"><img src="themes/dot.gif" title="Documentation" alt="Documentation" class="icon ic_b_help"></a><a href="index.php?route=/table/sql&sql_query=SELECT+%2A+FROM%0D%0A++++++++%28SELECT+id%0D%0AFROM+t1%0D%0AUNION+ALL%0D%0ASELECT+id%0D%0AFROM+t2%0D%0A%29+as+results&show_query=1&db=test_16988&table=t1"><span class="nowrap"><img src="themes/dot.gif" title="Edit" alt="Edit" class="icon ic_b_edit">&nbsp;Edit</span></a>    </p>
<p>
<code class="sql"><pre>
SELECT * FROM
        (SELECT id
FROM t1
UNION ALL
SELECT id
FROM t2
) as results
</pre></code>
</p>
<p>
    <strong>MySQL said: </strong><a href="./url.php?url=https%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F8.0%2Fen%2Fserver-error-reference.html" target="mysql_doc"><img src="themes/dot.gif" title="Documentation" alt="Documentation" class="icon ic_b_help"></a>
</p>
<code>#1046 - No database selected</code><br></div>

williamdes · 2021-09-08T07:36:45Z

Hi @iifawzi
Could you please help me on solving this one ?

iifawzi · 2021-09-08T13:05:06Z

Hi, I've investigated more on this, the issues is caused because for complex queries our analyzer doesn't know exactly what is the table name, which's leading to mark the complex queries as raw query

phpmyadmin/libraries/classes/Display/Results.php

Lines 4710 to 4732 in 63faf7e

    
                       // In case this query doesn't involve any tables, 
        
                       // implies only raw query is to be exported 
        
                       if (! $analyzed_sql_results['select_tables']) { 
        
                           $_url_params['raw_query'] = 'true'; 
        
                       } 
        
                       $_url_params['unlim_num_rows'] = $this->properties['unlim_num_rows']; 
        
                       /** 
        
                        * At this point we don't know the table name; this can happen 
        
                        * for example with a query like 
        
                        * SELECT bike_code FROM (SELECT bike_code FROM bikes) tmp 
        
                        * As a workaround we set in the table parameter the name of the 
        
                        * first table of this database, so that /table/export and 
        
                        * the script it calls do not fail 
        
                        */ 
        
                       if (empty($_url_params['table']) && ! empty($_url_params['db'])) { 
        
                           $_url_params['table'] = $dbi->fetchValue('SHOW TABLES'); 
        
                           /* No result (probably no database selected) */ 
        
                           if ($_url_params['table'] === false) { 
        
                               unset($_url_params['table']); 
        
                           } 
        
                       }

$analyzed_sql_results['select_tables'] will always be an empty array in case of complex queries - mentioned also above -
which's lead the query to be exported using exportRawQuery that doesn't know anything about the database.

phpmyadmin/libraries/classes/Plugins/Export/ExportCsv.php

Lines 366 to 369 in e355487

    
           public function exportRawQuery(string $errorUrl, string $sqlQuery, string $crlf): bool 
        
           { 
        
               return $this->exportData('', '', $crlf, $errorUrl, $sqlQuery); 
        
           }

The issue affects all of the plugins not only csv.
a fix for this might need making changes to how we're analyzing the queries :')

Those are my findings, what do you think @williamdes

This could solve the issue:
https://github.com/iifawzi/phpmyadmin/blob/ef840f18451837e58bcddf807ff8f4d73b15f974/libraries/classes/Display/Results.php#L4730-L4732 but i'm not considering it other than a workaround that might introduce/ have introduced more bugs because if the real query wasn't including any tables ( select 1 ) it wouldn't be marked as raw_queery, and although it will be exported as expected, but I think it shouldn't be handled this way.

duronrulez · 2021-09-08T22:21:32Z

Hey, dont know if relevant, but on previous versions that same query used to work fine for exporting.
If relevant i can try to figure out what version we were using before upgrading.

iifawzi · 2021-09-08T23:24:18Z

Hey, dont know if relevant, but on previous versions that same query used to work fine for exporting.
If relevant i can try to figure out what version we were using before upgrading.

Interesting, definitely could help! either I'm missing something, or there's a bad commit that messed this up.
cc @williamdes

duronrulez · 2021-09-09T06:15:17Z

The version that we previously used is 4.9.0.1, in that version the same query worked fine, when doing exports. Let me know if there is any other information i can provide to make identifying and resolving the issue easier.

duronrulez · 2021-10-05T08:27:58Z

@iifawzi @williamdes any news on this issue?

williamdes · 2021-11-25T19:35:29Z

Hi @duronrulez
I am too much behind sync with my GitHub emails, sorry for that
This issue is tagged for 5.1.2 so it will not be forgotten

If anyone can workout something that will be cool, else I will take care of this when I can spend more hours on the project

ahfeel · 2022-02-21T09:57:17Z

Impacted too, I think this issue is pretty urgent to be honest. All exports with subqueries are broken.
I also confirm that rolling back to 4.9.* works.

williamdes · 2022-02-28T08:36:50Z

Hi @iifawzi
Could you please have a second try ?
Let me know if you need help ;)

iifawzi · 2022-02-28T16:41:07Z

Working on it

iifawzi · 2022-02-28T22:04:35Z

I can still confirm the findings above, the issue is that we don't know the table name, and this is also mentioned here.

phpmyadmin/libraries/classes/Display/Results.php

Lines 4710 to 4732 in 63faf7e

    
                       // In case this query doesn't involve any tables, 
        
                       // implies only raw query is to be exported 
        
                       if (! $analyzed_sql_results['select_tables']) { 
        
                           $_url_params['raw_query'] = 'true'; 
        
                       } 
        
                       $_url_params['unlim_num_rows'] = $this->properties['unlim_num_rows']; 
        
                       /** 
        
                        * At this point we don't know the table name; this can happen 
        
                        * for example with a query like 
        
                        * SELECT bike_code FROM (SELECT bike_code FROM bikes) tmp 
        
                        * As a workaround we set in the table parameter the name of the 
        
                        * first table of this database, so that /table/export and 
        
                        * the script it calls do not fail 
        
                        */ 
        
                       if (empty($_url_params['table']) && ! empty($_url_params['db'])) { 
        
                           $_url_params['table'] = $dbi->fetchValue('SHOW TABLES'); 
        
                           /* No result (probably no database selected) */ 
        
                           if ($_url_params['table'] === false) { 
        
                               unset($_url_params['table']); 
        
                           } 
        
                       }

so, what's happening is that, we're marking almost every complex query (where we don't know exactly the name of the table) as a raw query, which then will be exported using the exportRawQuery method as mentioned in my previous comment.

it was all introduced at #15121.

Possible solutions:

Commenting the piece of code that set the type as raw.

I've tested it, all tests are passing, and I've also tested it manually and it worked as expected, the only difference is that for the real raw queries, such as SELECT 1 the export UI will show Exporting rows from "t1" table instead of exporting raw queries.

and imo, it's not an issue, it wouldn't affect the functionality, and everything will be exported correctly.

Trying to determine whether it's a real raw query or not:
This would require determining the table, if we're able to detect a table in the query, so it's not a raw query.

I've investigated more into the docs/code, and found that we can use the getFieldsMeta method
https://develdocs.phpmyadmin.net/phpmyadmin/PhpMyAdmin/DatabaseInterface.html#method_getFieldsMeta
to get more information about the query, and it includes the table name.

for the real raw queries, the table name output of this method, is empty, and for any complex query it seems to be able to detect a table name, so we can do something like this:

            $queryResult = $this->dbi->tryQuery($urlParams['sql_query']);
            $queryFieldsMeta = $this->dbi->getFieldsMeta($queryResult)[0];
            if (! $analyzedSqlResults['select_tables'] && $queryFieldsMeta->table == '') {
                $urlParams['raw_query'] = 'true';
            }

Although i'm not really sure of this approach, but it worked like a charm! the manual tests got exported and worked as expected.

the only issue of this approach is that when I attempted to run the unit tests, some of them failed with a strange error to me.
Not supported query
it seems to be occurring because of the tryQuery call. In fact I don't really know why and how, or whether it's an indicator that the approach is incorrect or not.

cc: @williamdes

waza-ari · 2022-03-24T20:04:23Z

Hi there, I've observed a similar issue, I'm not sure it is related though. If you use a table called "user" (I know that's not the best idea, but the product in question just names the table like this) and run a query involving this table without quoting the name like this:

SELECT email, firstname, lastname FROM user WHERE is_active = 1

It works nicely within PMA, but when using the export function, it runs into the same error as described above. Essentially, it complains about no database selected.

Note that the following query works:

SELECT email, firstname, lastname FROM `user` WHERE is_active = 1

boit8 · 2022-04-05T11:35:47Z

For the people in need of a temporary solution: What worked for me is to create a view of the query and export it from there.

seraos · 2022-07-02T21:05:53Z

For the people in need of a temporary solution: What worked for me is to create a view of the query and export it from there.

thanks! solved for me!

ahfeel · 2022-12-02T09:55:41Z

Jumping back almost 10 months later, still no fix and the milestone keeps being pushed away, while this looks to me as a VERY important bug. Exporting queries is a basic feature of PMA, and the fix proposed by @iifawzi sounds rather simple, could we have an update on what is blocking this so maybe we could help and contribute ?
Thanks

williamdes · 2022-12-02T10:24:05Z

Hi
There was quite a bit of work done on 5.2, I feel this should be also fixed before 5.2.1.

williamdes · 2022-12-13T21:14:47Z

Using #16988 (comment)

When I export as text (to browser, not as a file) it outputs this, it's another bug for this topic:
Edit, fixed with: d258e4e

-- phpMyAdmin SQL Dump
-- version 5.2.1-dev
-- https://www.phpmyadmin.net/
--
-- Hôte : mariadb108.williamdes.local
-- Généré le : mar. 13 déc. 2022 à 21:13
-- Version du serveur : 10.8.6-MariaDB-1:10.8.6+maria~ubu2204
-- Version de PHP : 8.1.13

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";

SELECT * FROM (SELECT id FROM t1 UNION ALL SELECT id FROM t2 ) as results  COMMIT;

Export as JSON gives some broken JSON containing the "no database selected error"

williamdes · 2022-12-13T22:37:26Z

Hi everybody,

I made a fix for the queries marked as "raw". There should not be any database not selected issue anymore.
It uses the database you are in when you execute the query.

The (phpMyAdmin 5.2+snapshot) also available as a non official docker image is updated with the fix, please try it and give feedback here 🙏🏻

ahfeel · 2022-12-16T16:52:08Z

Thank you very much @williamdes !!

williamdes · 2022-12-31T19:13:00Z

Hi there, I've observed a similar issue, I'm not sure it is related though. If you use a table called "user" (I know that's not the best idea, but the product in question just names the table like this) and run a query involving this table without quoting the name like this:
SELECT email, firstname, lastname FROM user WHERE is_active = 1
It works nicely within PMA, but when using the export function, it runs into the same error as described above. Essentially, it complains about no database selected.

Note that the following query works:
SELECT email, firstname, lastname FROM `user` WHERE is_active = 1

Hi @waza-ari
Is this now solved on the snapshot version?

/cc @iifawzi I think it may be fixed with your PR about using keywords as table names on the parser
Maybe we should add this query as a test case

iifawzi · 2023-01-01T13:46:14Z

I've tested it already without that PR, and the Parser is not complaining, but I will check that

williamdes added the Bug A problem or regression with an existing feature label Jul 4, 2021

williamdes added this to the 5.1.2 milestone Jul 4, 2021

williamdes added this to Needs triage in issues via automation Jul 4, 2021

williamdes moved this from Needs triage to to be fixed soon in issues Jul 4, 2021

williamdes moved this from to be fixed soon to High priority in issues Sep 8, 2021

williamdes added the help wanted label Nov 25, 2021

williamdes modified the milestones: 5.1.2, 5.1.3 Jan 10, 2022

ibennetch modified the milestones: 5.1.3, 5.1.4 Feb 11, 2022

ibennetch modified the milestones: 5.1.4, 5.2.1 May 11, 2022

williamdes added the affects/5.2 This issue or pull-request affects 5.2.x releases (and maybe further versions) label Oct 25, 2022

williamdes removed this from the 5.2.1 milestone Oct 25, 2022

williamdes added this to the 5.2.1 milestone Dec 2, 2022

williamdes moved this from High priority to Closed in issues Dec 13, 2022

MauricioFauth closed this as completed in 9c4a901 Dec 16, 2022

williamdes self-assigned this Dec 31, 2022

github-actions bot locked as resolved and limited conversation to collaborators Jan 2, 2024

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Export as CSV - No database selected #16988

Export as CSV - No database selected #16988

nunoperalta commented Jul 3, 2021 •

edited

Loading

williamdes commented Jul 4, 2021

nunoperalta commented Jul 4, 2021

taulantxhakli commented Jul 4, 2021

williamdes commented Jul 4, 2021

taulantxhakli commented Jul 4, 2021

williamdes commented Jul 4, 2021

nunoperalta commented Jul 4, 2021 •

edited by williamdes

Loading

duronrulez commented Sep 8, 2021

duronrulez commented Sep 8, 2021 •

edited by williamdes

Loading

williamdes commented Sep 8, 2021

iifawzi commented Sep 8, 2021 •

edited

Loading

duronrulez commented Sep 8, 2021

iifawzi commented Sep 8, 2021

duronrulez commented Sep 9, 2021

duronrulez commented Oct 5, 2021

williamdes commented Nov 25, 2021

ahfeel commented Feb 21, 2022 •

edited

Loading

williamdes commented Feb 28, 2022

iifawzi commented Feb 28, 2022

iifawzi commented Feb 28, 2022 •

edited

Loading

waza-ari commented Mar 24, 2022

boit8 commented Apr 5, 2022

seraos commented Jul 2, 2022 •

edited

Loading

ahfeel commented Dec 2, 2022

williamdes commented Dec 2, 2022 •

edited

Loading

williamdes commented Dec 13, 2022 •

edited

Loading

williamdes commented Dec 13, 2022

ahfeel commented Dec 16, 2022

williamdes commented Dec 31, 2022

iifawzi commented Jan 1, 2023

Export as CSV - No database selected #16988

Export as CSV - No database selected #16988

Comments

nunoperalta commented Jul 3, 2021 • edited Loading

Describe the bug

To Reproduce

Server configuration

Client configuration

williamdes commented Jul 4, 2021

nunoperalta commented Jul 4, 2021

taulantxhakli commented Jul 4, 2021

williamdes commented Jul 4, 2021

taulantxhakli commented Jul 4, 2021

williamdes commented Jul 4, 2021

nunoperalta commented Jul 4, 2021 • edited by williamdes Loading

duronrulez commented Sep 8, 2021

duronrulez commented Sep 8, 2021 • edited by williamdes Loading

williamdes commented Sep 8, 2021

iifawzi commented Sep 8, 2021 • edited Loading

duronrulez commented Sep 8, 2021

iifawzi commented Sep 8, 2021

duronrulez commented Sep 9, 2021

duronrulez commented Oct 5, 2021

williamdes commented Nov 25, 2021

ahfeel commented Feb 21, 2022 • edited Loading

williamdes commented Feb 28, 2022

iifawzi commented Feb 28, 2022

iifawzi commented Feb 28, 2022 • edited Loading

waza-ari commented Mar 24, 2022

boit8 commented Apr 5, 2022

seraos commented Jul 2, 2022 • edited Loading

ahfeel commented Dec 2, 2022

williamdes commented Dec 2, 2022 • edited Loading

williamdes commented Dec 13, 2022 • edited Loading

williamdes commented Dec 13, 2022

ahfeel commented Dec 16, 2022

williamdes commented Dec 31, 2022

iifawzi commented Jan 1, 2023

nunoperalta commented Jul 3, 2021 •

edited

Loading

nunoperalta commented Jul 4, 2021 •

edited by williamdes

Loading

duronrulez commented Sep 8, 2021 •

edited by williamdes

Loading

iifawzi commented Sep 8, 2021 •

edited

Loading

ahfeel commented Feb 21, 2022 •

edited

Loading

iifawzi commented Feb 28, 2022 •

edited

Loading

seraos commented Jul 2, 2022 •

edited

Loading

williamdes commented Dec 2, 2022 •

edited

Loading

williamdes commented Dec 13, 2022 •

edited

Loading