Skip to main content
30 votes
Accepted

How to retrieve the query I used to create a view?

View definitions are stored in the information schema: SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'yourviewname'
mustaccio's user avatar
  • 26.4k
26 votes

mysql to mariadb: unknown collation utf8mb4_0900_ai_ci

I've solved my issue by replacing all occurrences of utf8mb4_0900_ai_ci with utf8mb4_unicode_520_ci in my SQL dump file.
Francesco Borzi's user avatar
23 votes
Accepted

Output getting truncated in phpMyAdmin

Turns out it was phpMyAdmin - needed to show full texts. Just above the SQL output there is an 'Options' link. Click that and you will see Partial texts/Full texts radio buttons. Note, that when I ...
jscar's user avatar
  • 501
22 votes

mysql to mariadb: unknown collation utf8mb4_0900_ai_ci

In MariaDB use utf8mb4_unicode_520_ci instead, or you may even use uca1400_as_ci if you have a newer version of MariaDB. Don't use "_general_ci", as it does not correctly sort or compare ...
thomasrutter's user avatar
8 votes
Accepted

Foreign Keys and Data Import

You have several choices. You can either create the tables without the constraints & add them afterwards, or create the tables with the foreign keys & them import the data with foreign key ...
Philᵀᴹ's user avatar
  • 31.8k
6 votes
Accepted

How to switch from MariaDB to MySQL in WAMP server?

To pick MySQL or MariaDB, right click on the wampmanager icon in the system tray and you should see this this menu Please Select Components like I did please check the 2nd Screenshot
Arpit Jain's user avatar
5 votes

"Error creating foreign key (check data types)…" but they do match…

It was a bug with phpmyadmin. When you create a new constraint you usually are allowed to leave its name field blank, and it fills it for you. This time, for some still unknown reason, this didn't ...
Marshall Banana's user avatar
5 votes

How to retrieve the query I used to create a view?

You can use the documented feature SHOW CREATE VIEW view_name which is documented on the official dev.MySQL.com website: Reference: 13.7.5.14 SHOW CREATE VIEW Syntax (Version 5.5) 14.7.5.13 SHOW ...
John K. N.'s user avatar
5 votes

checking availability of rooms

This is a somewhat common logical error. ('$checkin' BETWEEN start_date AND end_date) OR ('$checkout' BETWEEN start_date AND end_date)); The problem is not the way the query is written, it's that ...
Michael - sqlbot's user avatar
5 votes

Why is ON DUPLICATE KEY not working as expected?

Any idea why this query doesn't work with table B? Do you have to insert/update every table column when running the query? Yes, UserName is a NOT NULL column and doesn't have a DEFAULT value. ...
ypercubeᵀᴹ's user avatar
5 votes
Accepted

Distribute n questions to m students with all students having the same questions but in different orders

TL;DR - eventually figured out how to do this in a single pass - that answer's at the end. I left the original answer in place in order for people here to maybe learn from my own (creaking ;-) ) ...
Vérace's user avatar
  • 30.2k
5 votes

Does this kind of data/field in this table needs normalization in MYSQL?

I would vote yes to normalize it. Units of measure, i.e. a UOM column, should literally be units of measure, e.g. "meters", "yards", "pounds", "tons" etc. I don'...
J.D.'s user avatar
  • 38.3k
4 votes
Accepted

DB Restart Taking too long, site is down

Without seeing any additional information, here is my best guess why MySQL is trying to shutdown for a long time: I suspect mysqld no longer has a socket file. About 1.5 years ago, I answered mysql ...
RolandoMySQLDBA's user avatar
4 votes

What is difference between SELECT * FROM table and SELECT * FROM table WHERE 1

Question 1 : What is the difference between these 2 queries, if it loads the same data. None. Question 2 : Why there is these much time difference, if it loads the same data. Within margin of ...
danblack's user avatar
  • 7,819
4 votes
Accepted

How to create a running balance column based on account number?

It seems you need in simple SELECT *, SUM(debit_balance - credit_balance) OVER (PARTITION BY account_no ORDER BY id) balance FROM acc_account_transactions fiddle
Akina's user avatar
  • 20.1k
4 votes

How can I make queries secret?

The Apache logs show what the Apache Web Server has been asked to do. It generally knows (nor cares) not a jot about SQL. Of any flavour. Please, please, please tell me you're not submitting raw SQL ...
Phill  W.'s user avatar
  • 8,915
4 votes
Accepted

Remove new lines in rows

If you are talking about fixing data that is already in the database, then you could just use the REPLACE() function in an UPDATE statement, something like this: UPDATE YourTable SET Col1 = ...
Andriy M's user avatar
  • 23.1k
3 votes
Accepted

Ideal configuration and structure for single db + single table + 20 million static records

All you're missing is an index on postcode. In your query you're doing this: where('postcode', '=', $pcode It almost certainly translates at the database layer to select a, b, c from table where ...
Cody Konior's user avatar
  • 3,508
3 votes

"Error creating foreign key (check data types)…" but they do match…

I got the same issue and happend to see this thread. I have a feeling that the newest version of phpmyadmin doesn't have this bug. The name for foreign key must be unique. You can create the same ...
Rakesh's user avatar
  • 31
3 votes

How to retrieve the query I used to create a view?

"show create view" will provide you the DDL to recreate the view exactly as it is (complete with select statement). show create view view_name_here \G \G is enhanced go format (displays columns ...
RMathis's user avatar
  • 482
3 votes

Exporting table from MySQL 5.7 to MySQL 5.6 (Error)

It seems that the id column is varchar(255) so from your config file enable the innodb_large_prefix on this instance of MySQL to resolve the problem but per the below, this explains why this is ...
IT Thug Ninja's user avatar
3 votes
Accepted

You do not have privileges to manipulate with the users!

According to comments in this Github issue report for phpMyAdmin, you probably need to set this option: $cfg['Servers'][$i]['DisableIS'] = true; in config.inc.php. Though it sounds like this is a ...
dbdemon's user avatar
  • 6,381
3 votes
Accepted

How to open a file in MySQL event scheduler?

You cannot or should not manipulate files within a store procedure (which is basically the same as an event, but with a schedule) due to scope and security reasons. MySQL events and stored procedures ...
jynus's user avatar
  • 14.9k
3 votes

Monthly Date and Hour wise report generation using PHP MYSQL

How to get data without dates which are not recorded? This is a common problem. The generic solution is Build a table with all possible dates (at least in the range needed). LEFT JOIN from that ...
Rick James's user avatar
  • 78.6k
3 votes

mysql to mariadb: unknown collation utf8mb4_0900_ai_ci

I replaced all utf8mb4_0900_ai_ci with utf8mb4_unicode_ci and it works. As FlipMcF stated, unicode is the better choice – general collation is outdated for it has flawed sorting.
Ti Hausmann's user avatar
3 votes

How to export specific columns from a SQL table

Using phpMyAdmin run a SQL query against the table that you want a column of data from and select only that column (something along the lines of SELECT cas_number FROM analytes). Scroll to the bottom ...
Dave's user avatar
  • 403
3 votes

Mysql 8 accidentally lost ROOT user all privileges in localhost wampserver 3.2

Thank god,Finally it worked out. first I created a file named as mysql-init.txt in c drive.The file contains update mysql.user set Select_priv='Y', Insert_priv='Y', Update_priv='Y', ...
BluezTechz's user avatar
3 votes
Accepted

Export and import a table without a specific column

Another solution is to use LOAD DATA [LOCAL] INFILE, but override the columns list so the auto-increment value is inserted into a dummy variable: Here's a demo: I have a text file I call c.csv: 1 ...
Bill Karwin's user avatar
  • 15.2k
3 votes
Accepted

Optimize a query multiple tables & Multiple conditions

I suggest adding the following composite keys: ALTER TABLE erp_salesorderproductsdetails ADD INDEX `idx_1` (`salesorder_id`,`category_name`); ALTER TABLE geopos_productall ADD INDEX `idx_2` (`...
Ergest Basha's user avatar
  • 4,025
2 votes

There is no export as SQL option

I'm using phpmyadmin 4.8.5 and the export to sql option still not showing. Two potential workarounds: Create a new table with the results and then export that table: e.g: CREATE TABLE ...
Danny Thompson's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible