Dev Stack Source

Answer : DATE_FORMAT(FROM_UNIXTIME(`user.registration`), '%e %b %Y') AS 'date_formatted' Convert timestamp to date in MYSQL Make the table with an integer timestamp: mysql> create table foo(id INT, mytimestamp INT(11)); Query OK, 0 rows affected (0.02 sec) Insert some values mysql> insert into foo values(1, 1381262848); Query OK, 1 row affected (0.01 sec) Take a look mysql> select * from foo; +------+-------------+ | id | mytimestamp | +------+-------------+ | 1 | 1381262848 | +------+-------------+ 1 row in set (0.00 sec) Convert the number to a timestamp: mysql> select id, from_unixtime(mytimestamp) from foo; +------+----------------------------+ | id | from_unixtime(mytimestamp) | +------+----------------------------+ | 1 | 2013-10-08 16:07:28 | +------+----------------------------+ 1 row in set (0.00 sec) Convert it into a readable format: mysql> select id, from_unixtime(mytimestamp, '%Y %D %M %H:%i:%s') from foo; +------+...

Answer : You can run mysql in batch mode, as noted in the documentation. mysql -h host -u user -p < batch-file Basically you use a file containing all of your commands as an input parameter - mysql will execute the contents of that file. Edit: If you want to build your query on the fly, you can always have your batch file write out a query to a temporary file that you can then load for execution by mysql. For example: echo show tables from test > C:\path\to\file.sql mysql -h host -u user -p < C:\path\to\file.sql Run mysql with -e option: mysql -h host -u user -p -e 'SHOW TABLES FROM test' Perhaps you may want to try a small trick that emerged from other question in this forum (that was deleted unfortunately). You may insert the input for a command directly in the lines below the command and then execute the file NOT as Batch file, but as input por cmd.exe (this is similar to a here document in Unix). For example: script.TXT: @echo off mysql -u user -p *enter in th...

Answer : I found that the mysql.sock is deleted when the mysql service is stoped and mysqld_safe can't create it (I couldn't find the reason), so my solution was back up the sock folder and restore before start mysqld_safe Start server $ sudo service mysql start Go to sock folder $ cd /var/run Back up the sock $ sudo cp -rp ./mysqld ./mysqld.bak Stop server $ sudo service mysql stop Restore the sock $ sudo mv ./mysqld.bak ./mysqld Start mysqld_safe $ sudo mysqld_safe --skip-grant-tables --skip-networking & Init mysql shell mysql -u root Change password FLUSH PRIVILEGES; SET PASSWORD FOR root@'localhost' = PASSWORD('my_new_password'); For Ubuntu 19 with MySQL 8.0.17-0ubuntu2, what ended up working for me was a combination of many answers: In the MySQL's configuration file ( /etc/mysql/mysql.conf.d/mysqld.cnf on my machine), under [mysqld] , add: skip-grant-tables = 1 plugin-load-add = auth_socket.so Restart the ...

Answer : You can chain REPLACE functions: select replace(replace('hello world','world','earth'),'hello','hi') This will print hi earth . You can even use subqueries to replace multiple strings! select replace(london_english,'hello','hi') as warwickshire_english from ( select replace('hello world','world','earth') as london_english ) sub Or use a JOIN to replace them: select group_concat(newword separator ' ') from ( select 'hello' as oldword union all select 'world' ) orig inner join ( select 'hello' as oldword, 'hi' as newword union all select 'world', 'earth' ) trans on orig.oldword = trans.oldword I'll leave translation using common table expressions as an exercise for the reader ;) Cascading is the only simple and straight-forward solution to mysql for multiple character replacement. UPDATE table1...

Answer : Use findAll() with include() and sequelize.fn() for the COUNT : Location.findAll({ attributes: { include: [[Sequelize.fn("COUNT", Sequelize.col("sensors.id")), "sensorCount"]] }, include: [{ model: Sensor, attributes: [] }] }); Or, you may need to add a group as well: Location.findAll({ attributes: { include: [[Sequelize.fn("COUNT", Sequelize.col("sensors.id")), "sensorCount"]] }, include: [{ model: Sensor, attributes: [] }], group: ['Location.id'] }) Location.findAll({ attributes: { include: [[Sequelize.fn("COUNT", Sequelize.col("sensors.id")), "sensorCount"]] }, include: [{ model: Sensor, attributes: [] }] }); and it works. but when i add "limit", i got error: sensors undefined For Counting associated entries with Sequelize Location.fi...

Answer : CREATE TEMPORARY TABLE IF NOT EXISTS table2 AS (SELECT * FROM table1) From the manual found at http://dev.mysql.com/doc/refman/5.7/en/create-table.html You can use the TEMPORARY keyword when creating a table. A TEMPORARY table is visible only to the current session , and is dropped automatically when the session is closed. This means that two different sessions can use the same temporary table name without conflicting with each other or with an existing non-TEMPORARY table of the same name. (The existing table is hidden until the temporary table is dropped.) To create temporary tables, you must have the CREATE TEMPORARY TABLES privilege. In addition to psparrow's answer if you need to add an index to your temporary table do: CREATE TEMPORARY TABLE IF NOT EXISTS temp_table ( INDEX(col_2) ) ENGINE=MyISAM AS ( SELECT col_1, coll_2, coll_3 FROM mytable ) It also works with PRIMARY KEY Use this syntax: CREATE TEMPORARY TABLE t1 (select * from t2);

Answer : The error Error on rename of ... errno: 150 - Foreign key constraint is incorrectly formed) happens because you are trying to drop a referenced primary key, even though you are disabling foreign key constraint checking with SET FOREIGN_KEY_CHECKS=0; Disabling foreign key checks would allow you to temporarily delete a row in the currency table or add an invalid currencyId in the foreign key tables, but not to drop the primary key. Changing a PRIMARY KEY which is already referenced by other tables isn't going to be simple, since you risk losing referential integrity between the tables and losing the relationship between data. In order to preserve the data, you'll need a process such as: Add a new Foreign key column ( code ) to each FK table Map the code foreign key from the previous currencyId via an update Drop the existing foreign key Drop the old currencyId foreign key column Once all FK's have been dropped, change the primary key on the...

Answer : Solution 1: First of all, don't just take my word for it! Test my suggestion out with this: select CONCAT('alter table ',TABLE_SCHEMA,'.',TABLE_NAME,' charset=utf8;') from information_schema.TABLES WHERE TABLE_SCHEMA != 'information_schema' limit 10; select CONCAT('alter table ',TABLE_SCHEMA,'.',TABLE_NAME,' alter column ',COLUMN_NAME,' charset=utf8;') from information_schema.COLUMNS WHERE TABLE_SCHEMA != 'information_schema' limit 10; If you feel good with the outcome of that, remove the limit clauses and save the output to an SQL script or, get fancy and pipe the output directly to mysql similar to what I demonstrate here. That would look like this: mysql -B -N --host=prod-db1 --user=admin --password=secret -e "select CONCAT('alter table ',TABLE_SCHEMA,'.',TABLE_NAME,' charset=utf8;') from information_schema.TABLES WHERE TABLE_SCHEMA != 'information_schema...

Answer : You can try to use sed in order to extract only the table you want. Let say the name of your table is mytable and the file mysql.dump is the file containing your huge dump: $ sed -n -e '/CREATE TABLE.*`mytable`/,/Table structure for table/p' mysql.dump > mytable.dump This will copy in the file mytable.dump what is located between CREATE TABLE mytable and the next CREATE TABLE corresponding to the next table. You can then adjust the file mytable.dump which contains the structure of the table mytable , and the data (a list of INSERT ). I used a modified version of uloBasEI's sed command. It includes the preceding DROP command, and reads until mysql is done dumping data to your table (UNLOCK). Worked for me (re)importing wp_users to a bunch of Wordpress sites. sed -n -e '/DROP TABLE.*`mytable`/,/UNLOCK TABLES/p' mydump.sql > tabledump.sql This can be done more easily? This is how I did it: Create a temporary database (e.g. restore): ...

Answer : Use: SELECT SUM(CASE WHEN t.your_column IS NULL THEN 1 ELSE 0 END) AS numNull, SUM(CASE WHEN t.your_column IS NOT NULL THEN 1 ELSE 0 END) AS numNotNull FROM YOUR_TABLE t That will sum up the column NULL & not NULL for the entire table. It's likely you need a GROUP BY clause, depending on needs.

Answer : Actually if you create func: create function p1() returns INTEGER DETERMINISTIC NO SQL return @p1; and view: create view h_parm as select * from sw_hardware_big where unit_id = p1() ; Then you can call a view with a parameter: select s.* from (select @p1:=12 p) parm , h_parm s; I hope it helps. CREATE VIEW MyView AS SELECT Column, Value FROM Table; SELECT Column FROM MyView WHERE Value = 1; Is the proper solution in MySQL, some other SQLs let you define Views more exactly. Note: Unless the View is very complicated, MySQL will optimize this just fine.

Answer : I think one can end up in this position with older versions of mysql already installed. I had the same problem and none of the above solutions worked for me. I fixed it thus: Used brew's remove & cleanup commands, unloaded the launchctl script, then deleted the mysql directory in /usr/local/var , deleted my existing /etc/my.cnf (leave that one up to you, should it apply) and launchctl plist Updated the string for the plist. Note also your alternate security script directory will be based on which version of MySQL you are installing. Step-by-step: brew remove mysql brew cleanup launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist rm ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist sudo rm -rf /usr/local/var/mysql I then started from scratch: installed mysql with brew install mysql ran the commands brew suggested: (see note: below) unset TMPDIR mysql_install_db --verbose --user=`whoami` --basedir="$(brew --prefix mysql)...

Answer : From what you describe, it seems you have UTF-8 data that was originally stored as Latin-1 and then not converted correctly to UTF-8. The data is recoverable; you'll need a MySQL function like convert(cast(convert(name using latin1) as binary) using utf8) It's possible that you may need to omit the inner conversion, depending on how the data was altered during the encoding conversion. After i searched about an hour or two for this answer. I needed to migrate a old tt_news db from typo into a new typo3 version. I already tried convert the charset in the export file and import it back, but didn't get it working. Then i tried the answer above from ABS and startet a update on the table: UPDATE tt_news SET title=convert(cast(convert(title using latin1) as binary) using utf8), short=convert(cast(convert(short using latin1) as binary) using utf8), bodytext=convert(cast(convert(bodytext using latin1) as binary) using utf8) WHERE 1 You can also convert im...

Answer : Copy config.sample.inc.php to config.inc.php . In most cases you will find the config file on linux: /etc/phpmyadmin/config.inc.php on mac: /Library/WebServer/Documents/phpmyadmin/config.inc.php If you are trying to log in as root, you should have the following lines in your config: c f g [ ′ S e r v e r s ′ ] [ cfg['Servers'][ c f g [ ′ S er v er s ′ ] [ i]['user'] = 'root'; c f g [ ′ S e r v e r s ′ ] [ cfg['Servers'][ c f g [ ′ S er v er s ′ ] [ i]['AllowNoPassword'] = true; According to this: https://www.simplified.guide/phpmyadmin/enable-login-without-password This $cfg['Servers'][$i]['AllowNoPassword'] = TRUE; should be added twice in /etc/phpmyadmin/config.inc.php if (!empty($dbname)) { // other configuration options $cfg['Servers'][$i]['AllowNoPassword'] = TRUE; // it should be placed before the following line $i++; } // other configuration options $cf...

Answer : You have installed MySQLWorkbench as a Snap package. You want to store the database password(s) in the Gnome Passwords & Keys facility. However, a Snap package is sandboxed ; it is not by default allowed to access this service. When you choose "Store in keychain" MySQLWorkbench is blocked by AppArmor. You need to enter a command to allow this package to access the service. The command is: sudo snap connect mysql-workbench-community:password-manager-service :password-manager-service I got this from the discussion at this site. Go to app store . Search for mysql-workbench . Click on permission . Enable Read, add, change, or remove saved password̀s

Answer : You're doing it right. The bound parameters are the one declared in a "prepared statement" using ?. Then they are bound using execute() with their value as a parameter to be bound to the statement. The protection comes from using bound parameters, not from using prepared statement Means it is not enough just to use prepare() but keep all variables in the query like this: $sql = $db->prepare("SELECT * FROM employees WHERE name ='$name'"); $sql->execute(); $rows = $sql->fetchAll(); Someone who said that meant "although technically you are using a prepared statement, you aren't binding variables to it". So it makes the query vulnerable all the same. To be protected, you have to substitute all variables in the query with placeholders, and then bind them: $sql = $db->prepare("SELECT * FROM employees WHERE name = ?"); $sql->bindParam(1, $name); $sql->execute(); $rows = $sql->fetchAll();...

Answer : Try to use this on your SQL file: sed 's/CREATE TABLE/CREATE TABLE IF NOT EXISTS/g' <file-path> or to save sed -i 's/CREATE TABLE/CREATE TABLE IF NOT EXISTS/g' <file-path> it's not ideal but it works :P According to one source, mysqldump does not feature this option. You could use the --force option when importing the dump file back, where MySQL will ignore the errors generated from attempts to create duplicate tables. However note that with this method, other errors would be ignored as well. Otherwise, you can run your dump file through a script that would replace all occurrences of CREATE TABLE with CREATE TABLE IF NOT EXISTS . Using sed as described by @Pawel works well. Nevertheless you might not like the idea of piping your data through more potential error sources than absolutely necessary. In this case one may use two separate dumps: first dump containing table definitions ( --no-data --skip-add-drop-table ) second dump with only da...

Answer : Without Converting you can find Maximum using this query select max(cast(stuff as decimal(5,2))) as mySum from test; check this SQLfiddle your demo table: create table test ( name varchar(15), stuff varchar(10) ); insert into test (name, stuff) values ('one','32.43'); insert into test (name, stuff) values ('two','43.33'); insert into test (name, stuff) values ('three','23.22'); Your Query: For SQL Server, you can use: select max(cast(stuff as decimal(5,2))) as mySum from test; I think you need to try doing something like this on your MySQL if you have admin privilege on your MySQL. ALTER TABLE tablename MODIFY columnname DECIMAL(M,D) for the M,D variables, read this - http://dev.mysql.com/doc/refman/5.0/en/fixed-point-types.html And MySQL should be able to automatically converting a text to a numeric. Just that the data type in MySQL might not be a decimal yet that's why you can't store any decimal. Be aware that...

Answer : Update in 2019-10-29 As mentions by @Manuel Jordan in comments, utf8mb4_0900_ai_ci is the new default in MySQL 8.0 , so the following is now again a better practice: CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; Answer before 2019-10-29 Note: The following is now considered a better practice (see bikeman868's answer): CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; Original answer: Try this: CREATE DATABASE mydatabase CHARACTER SET utf8 COLLATE utf8_general_ci; For more information, see Database Character Set and Collation in the MySQL Reference Manual. You should use: CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; Note that utf8_general_ci is no longer recommended best practice. See the related Q & A: What's the difference between utf8_general_ci and utf8_unicode_ci on Stack Overflow.

Answer : Please run an SHOW CREATE TABLE course; to make sure instructorID is the name of foreign key constraint . Additional: The error means MySQL searches for a foreign key constraint named "InstructorID" but there is no constraint with such name, maybe this is your column name, but you have to use the constraint name to delete foreign keys. After you run SHOW CREATE table course; you should find the fk symbol which is commonly like the one bellow: (course_ibfk_1) it may differ according to your mysql version you are using then drop the foreign key using the fk symbol as follow : alter table course drop foreign key course_ibfk_1; You need to delete the 'foreign key constraint' and the 'key'. Alter Table <table name> drop foreign key <constraint_name> Alter table <table name> drop key <column name>