Export as CSV - No database selected

[nunoperalta]

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:

1. Go to a database, then table
2. Open the SQL tab, type a SQL, get results
3. Click "Export" below the results
4. 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]

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

[nunoperalta]

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]

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]

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

• 5.2.0-dev
• 5.1.x-dev

[taulantxhakli]

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]

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]

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]

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

[duronrulez]

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]

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

[iifawzi]

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 https://github.com/phpmyadmin/phpmyadmin/blob/63faf7ea413173f5a00e24d59746a8f7dba365e3/libraries/classes/Display/Results.php#L4710-L4732 $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. https://github.com/phpmyadmin/phpmyadmin/blob/e3554879ef7a4879446db25669563df004f832a2/libraries/classes/Plugins/Export/ExportCsv.php#L366-L369

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]

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]

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]

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]

@iifawzi @williamdes any news on this issue?

[williamdes]

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]

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]

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

[iifawzi]

Working on it

[iifawzi]

I can still confirm the findings above, the issue is that we don't know the table name, and this is also mentioned here. https://github.com/phpmyadmin/phpmyadmin/blob/63faf7ea413173f5a00e24d59746a8f7dba365e3/libraries/classes/Display/Results.php#L4710-L4732

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 https://github.com/phpmyadmin/phpmyadmin/pull/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]

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]

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.

[rstecinfo]

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]

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]

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

[williamdes]

Using https://github.com/phpmyadmin/phpmyadmin/issues/16988#issuecomment-914992088

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

-- 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]

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]

Thank you very much @williamdes !!

[williamdes]

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]

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