Podcast #128: We chat with Kent C Dodds about why he loves React and discuss what life was like in the dark days before Git. Listen now.
19

phpmyadmin Perhaps you could just select the phpMyAdmin Operations tab: In phpMyAdmin, click on the table you want to reset or change the AUTO_INCREMENT value Click on the Operations Tab In the Table Options box find the auto_increment field. Enter the new auto_increment starting value Click on the Go button for the Table Options box. Since this one of ...


12

First you need to do is run this query: SELECT user,host FROM mysql.user WHERE super_priv='Y' AND CONCAT(user,'@',host) <> 'root@localhost'; This will list all users that have SUPER privilege. Most users that do application-related DB processing do not require this privilege. According to the MySQL Documentation, those with SUPER privilege can do the ...


11

I can't say anything about cPanel and phpmyadmin but In general i can do it by writing a simple script. I have written a shell script for you #!/bin/bash # mysql credential user="root" pass="root" # list of all databases all_dbs="$(mysql -u $user -p$pass -Bse 'show databases')" for db in $all_dbs do if test $db != "...


11

Using SQL from the mysql command-line: SELECT * from YOURTABLE WHERE status=0 and id>20 INTO OUTFILE 'yourtable.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'; or using mysqldump with the --where= option: mysqldump -u youruser -p yourdbname yourtablename --where="status=0 and id>20">yourtable.sql Using phpMyAdmin you can ...


11

As a supplement to the other answer(s), I'd prefer a more visual approach: Click the table you want to change. Click Operations: Look under "Table options": Also note that:  –§–  You cannot reset the counter to a value less than or equal to the value that is currently in use. For both InnoDB and MyISAM, if the value is less ...


11

(Regarding PostgreSQL 9.3 and MySQL 5.6, written in 2014; if you're looking at other versions, this may be outdated): Lots more features. CHECK constraints True SERIALIZABLE isolation Arrays (including index support for arrays) Window functions (lead, lag, row_number, etc) Common table expressions (WITH queries) including recursive CTEs and writeable CTEs ...


11

View definitions are stored in the information schema: SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'yourviewname'


9

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 went back to get the screenshot it was still set to Full Texts so I assume there is a cookie being set which may explain why it was different in one database to ...


7

Using a trigger You can't use functions as defaults in MySQL, except for CURRENT_TIMESTAMP for TIMESTAMP columns


7

Try running this and you might be able to cut/paste the output back into phpMyAdmin SELECT CONCAT('UPDATE ', schema_name, '.\'wp_users\' SET \'user_pass\' = MD5(\'somepassword\') WHERE \'user_login\' =\'admin\' LIMIT 1;') FROM information_schema.schemata WHERE schema_name NOT IN ('information_schema','mysql','performance_schema','test'); or, if you ...


7

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 checks disabled - simply run SET FOREIGN_KEY_CHECKS=0; in your mysql session to temporarily disable them. For example: mysql> insert into favourite values( 1,...


6

Because you granted it access without password... If a user has no password, logging in with one will always fail. Set the password for user, only then you'll be able to use -p In addition, you might want to remove the Any user.


6

In reality, those three user accounts are actually quite dangerous. They pose a very great threat to test databases. Unfortunately, mysql comes with full access to test databases. How can you find them ? Run this query: SELECT user,host,db from mysql.db; Upon installation of mysql, you will see two rows that give full access to any database named 'test' ...


6

If you are doing a range of consecutive dates, there is no need to use the IN operator. If you use this to compute midnight seven days ago DATE(NOW()) - INTERVAL 7 DAY + INTERVAL 0 SECOND here is what you get back: mysql> SELECT DATE(NOW()) - INTERVAL 7 DAY + INTERVAL 0 SECOND; +--------------------------------------------------+ | DATE(NOW()) - ...


6

The passwords you're talking about are passwords for MySQL users. You don't really set a password for a database or table. You can effectively implement different passwords for different DBs by creating multiple users with different passwords and only granting access to specific databases for each user. When people say that you should set a password for ...


6

No, you can't. Not out of the box. Possible workarounds: Triggers (an AFTER INSERT trigger). Disadvantages: Plain horror. Comes with all the other disadvantages of triggers, like maintenance and debugging nightmares. Advantages: You can have FOREIGN KEY constraints that reference this column. You can update the first and the second id columns ...


5

select DB, go to export, select tables that you want the structure (top left),deselect "DATA"(middle-center) and select a name and you will be able to export only the structure. or go to your "database" then "check all" (just below the tables) and change the option from "With Selected:" to "print view". it will show the relations too, and it will looks ...


5

The automated way of doing so is by defining foreign keys on your tables, with a DELETE CASCADE in your case. This way, a DELETE on the master table will propagate and delete matching rows from all derived tables. You will have to use InnoDB tables for that. I assume your tables do not have foreign keys at the moment, otherwise you wouldn't ask. In which ...


5

I had the same error, trying to debug locally some application that connects to remote DBs. When using php < 5.3, everything worked. When using php 5.3 or greater, error shows up. After hours spent reading and tweaking, i realized this only occurred when connecting as a certain user. All databases i use have new password length (41). I changed the ...


5

This is quite an easy one really (one of those "Doh" moments :-) ). You set it in my.cnf as described here: [mysqld] .. .. other_stuff .. event_scheduler=on .. .. more_stuff .. (or my.ini if you're running Windows as pointed out below) From the MySQL documentation here, you have: When the Event Scheduler is ON, the event scheduler thread is listed ...


5

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. Therefore you need to provide a value for every row INSERT. Not for UPDATE though, the column can be updated or not, depending on your requirements. Is it a PHP ...


5

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 ;-) ) thought processes! Update: there's an alternative answer provided below. This proved to be quite tricky. To solve this, I did the following (see fiddle here) I ...


4

DELETE B.* FROM ( SELECT playerId FROM tbl_players WHERE playerCountry = 'lt' ) A INNER JOIN tbl_highscores B USING (playerId); Make sure you have this index ALTER TABLE tbl_players ADD INDEX CountryIDIndex (playerCountry,playerId);


4

Sorry, that you had such an accident. If you have binary logs on, you could try the recovery options described here: Point in time recovery Another thing is to restore a backup, hopefully, you have one. Changes made after this, could be restored as mentioned above. Good luck!


4

I guess you want to either insert a new row with the counter status_likes = 1 or incresase it by 1 when a row with status_id already exists. If there is a UNIQUE constraint on member_status_like_count (status_id), you can use INSERT ... ON DUPLICATE KEY UPDATE: CREATE TRIGGER member_status_likes__AFTER_INSERT__UPSERT__like_count AFTER INSERT ON ...


4

Yes, if you use phpMyAdmin on your WAMP server, you can use the Export tab to dump your database and all the data in it. On the Export tab, choose the Custom export method. This opens a bunch of options. Under Save Output to a File, choose Compression and you get to choose zipped from a drop-down. Then you can Import that zip to another MySQL instance on ...


4

WAMPServer is primarily designed for use on a single machine i.e. a developer writing and testing code locally on that PC. So to protect the beginner from his/her self the Apache & MySQL configuration by default is setup so no access can be made to the Apache or MySQL server form anywhere but the PC running Apache and MySQL. When changing this ...


4

Data is present in data folder of MySQL which is usually in ProgramData for windows installations. look into "C:\ProgramData\MySQL\MySQL Server 5.6"..as programdata usually have the files on by default


4

Changing the MySQL settings worked. This is what I changed, exactly: In php.ini Find: post_max_size = 8M upload_max_filesize = 2M max_execution_time = 30 max_input_time = 60 memory_limit = 8M Change to: post_max_size = 750M upload_max_filesize = 750M max_execution_time = 5000 max_input_time = 5000 memory_limit = 1000M In my.ini (MySQL File) Under ...


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