Newbe Dev Stack

Answer : Yes, it works but with some quirks. MySQL uses the same fileformats across platforms so all you need is to share the data directory. One problem is that the data directory need to have mysql as owner and group in ubuntu. And Windows is case-insensitive and Linux is case-sensitive so keep all names uniform: either the whole name lowercase or uppercase but do not mix them. From start to finish; if you already have things set up this might need some tweaking to fit your setup: Install and setup MySQL on both systems. Stop the mysql server if it is running. Make a new NTFS partition. Mark the device name (let's call it sdXN for now). Move the mysql data directory from Ubuntu to the new partition. mkdir /{mountpoint}/mysql_data sudo mv /var/lib/mysql /{mountpoint/mysql_data using mv saves permissions. Make a new mysql directory sudo mkdir /var/lib/mysql Mount the NTFS partition at /var/lib/mysql . Change the devicename to what it got when you created the NT...

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

Answer : Something like Decimal(19,4) usually works pretty well in most cases. You can adjust the scale and precision to fit the needs of the numbers you need to store. Even in SQL Server, I tend not to use " money " as it's non-standard. The only thing you have to watch out for is if you migrate from one database to another you may find that DECIMAL(19,4) and DECIMAL(19,4) mean different things ( http://dev.mysql.com/doc/refman/5.1/en/precision-math-decimal-changes.html ) DBASE: 10,5 (10 integer, 5 decimal) MYSQL: 15,5 (15 digits, 10 integer (15-5), 5 decimal) Assaf's response of Depends on how much money you got... sounds flippant, but actually it's pertinant. Only today we had an issue where a record failed to be inserted into our Rate table, because one of the columns (GrossRate) is set to Decimal (11,4), and our Product department just got a contract for rooms in some amazing resort in Bora Bora, that sell for several million Pacif...

Answer : This is not possible in MySQL. You can create a required number of parameters and do UPDATE ... IN (?,?,?,?). This prevents injection attacks (but still requires you to rebuild the query for each parameter count). Other way is to pass a comma-separated string and parse it. You could build up the parametrised query "on the fly" based on the (presumably) variable number of parameters, and iterate over that to pass them in. So, something like: List foo; // assuming you have a List of items, in reality, it may be a List<int> or a List<myObject> with an id property, etc. StringBuilder query = new StringBuilder( "UPDATE TABLE_1 SET STATUS = ? WHERE ID IN ( ?") for( int i = 1; i++; i < foo.Count ) { // Bit naive query.Append( ", ?" ); } query.Append( " );" ); MySqlCommand m = new MySqlCommand(query.ToString()); for( int i = 1; i++; i < foo.Count ) { m.Parameters.Add(new MySqlParameter(...)); } You cann...

Answer : Solution 1: If you've got access to the mysql database, you can change the grant tables directly: UPDATE mysql.user SET Host='%' WHERE Host='localhost' AND User='username'; ...and an analogous UPDATE -statement to change it back. Also you might need to make changes to the mysql.db table as well: UPDATE mysql.db SET Host='%' WHERE Host='localhost' AND User='username'; and then flush to apply the privileges: FLUSH PRIVILEGES; Solution 2: Best answer on Stackoverflow suggesting to use RENAME USER which copy the user privileges. Using Data Control Language (statements as GRANT, REVOKE, RENAME and so on) does not require FLUSH PRIVILEGES; and is required in architecture like Galera or Group Replication in MySQL versions having MyISAM tables in mysql database because MyISAM tables are not replicated. Solution 3: I stumbled across this one, too, and the proposed solution didn't work, since the datab...

Answer : From my experience -- Sure, you can use MySQL Workbench with MariaDB. However, I have tried basic functionalities only, like queries, schema design etc. Not sure about compatibility of advanced features. Just to list a few other options: MySQL Workbench Heidi Sql SQLyog So my experiences are, yes you can use MySQL Workbench for MariaDB database designs. However I needed to change the "Default Target MySQL Version" to 5.7 . This can be done by going to: Edit->Preferences in the menu. And finally to Modeling->MySQL. Since the latest MySQL version, v8.x, the SQL statements are not compatible with MariaDB statements (like creating an index). MariabDB creating an index on a table: INDEX `fk_rsg_sub_level_rsg_top_level1_idx` (`rgs_top_level_id` ASC) vs MySQL: INDEX `fk_rsg_sub_level_rsg_top_level1_idx` (`rgs_top_level_id` ASC) VISIBLE MariaDB can't handle this VISIBLE keyword in this example. Using an old MySQL Version, MySQL Workbench...

Answer : I'm guessing Laravel can't determine the plural form of the word you used for your table name. Just specify your table in the model as such: class Cotizacion extends Model{ public $table = "cotizacion"; Check your migration file, maybe you are using Schema::table, like this: Schema::table('table_name', function ($table) { // ... }); If you want to create a new table you must use Schema::create: Schema::create('table_name', function ($table) { // ... }); I faced this problem too in laravel 5.2 and if declaring the table name doesn't work,it is probably because you have some wrong declaration or mistake in validation code in Request (If you are using one)

Answer : Personally, I would use the BOOLEAN for a boolean value. But keep in mind the caveat that in MySQL, BOOLEAN is just a synonym of TINYINT(1) [src]. This, of course, means you can have values other than 0 or 1 if you are not careful. To avoid this, you can use the aliases TRUE and FALSE when inserting and updating data, as those map to 1 and 0 respectively. For the sake of being complete, prior to 5.0.3, BIT was also a synonym of TINYINT(1) .

Answer : If you have that same problem in MySql 5.7.+ : Access denied for user 'root'@'localhost' it's because MySql 5.7 by default allow to connect with socket, which means you just connect with sudo mysql . If you run sql : SELECT user,authentication_string,plugin,host FROM mysql.user; then you will see it : +------------------+-------------------------------------------+-----------------------+-----------+ | user | authentication_string | plugin | host | +------------------+-------------------------------------------+-----------------------+-----------+ | root | | auth_socket | localhost | | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost | | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost | | debian-sys-maint | *497C3D7B50479A812B89CD12EC3...

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 : In general: Database and table names are not case sensitive in Windows, and case sensitive in most varieties of Unix. In MySQL, databases correspond to directories within the data directory. Each table within a database corresponds to at least one file within the database directory. Consequently, the case sensitivity of the underlying operating system plays a part in the case sensitivity of database and table names. One can configure how tables names are stored on the disk using the system variable lower_case_table_names (in the my.cnf configuration file under [mysqld]). Read the section: 10.2.2 Identifier Case Sensitivity for more information. Database and table names are not case sensitive in Windows, and case sensitive in most varieties of Unix or Linux. To resolve the issue, set the lower_case_table_names to 1 lower_case_table_names=1 This will make all your tables lowercase, no matter how you write them. It depends upon lower_case_table_nam...

Answer : Having a look at MySql docs I've found a warning about foreign_key_keys : Warning With foreign_key_checks=0, dropping an index required by a foreign key constraint places the table in an inconsistent state and causes the foreign key check that occurs at table load to fail. To avoid this problem, remove the foreign key constraint before dropping the index (Bug #70260). IMHO you should drop FOREIGN KEY before DROP the COLUMN. ALTER TABLE `user` DROP FOREIGN KEY `FK_G38T6P7EKUXYWH1`; ALTER TABLE `user` DROP COLUMN `region_id`; I've set up a rextester example, check it here.

Answer : If replace(lower()) doesn't work, you'll need to create another function. My 2 cents. Since many people have upgraded from MySQL to MariaDB those people will have available a new function called REGEXP_REPLACE . Use it as you would a normal replace, but the pattern is a regular expression. This is a working example: UPDATE `myTable` SET `myField` = REGEXP_REPLACE(`myField`, '(?i)my insensitive string', 'new string') WHERE `myField` REGEXP '(?i)my insensitive string' The option (?i) makes all the subsequent matches case insensitive (if put at the beginning of the pattern like I have then it all is insensitive). See here for more information: https://mariadb.com/kb/en/mariadb/pcre/ Edit: as of MySQL 8.0 you can now use the regexp_replace function too, see documentation: https://dev.mysql.com/doc/refman/8.0/en/regexp.html Alternative function for one spoken by fvox. DELIMITER | CREATE FUNCTION case_insensitive_replace ( REPLA...

Answer : SELECT * FROM ( (SELECT * FROM user_relation WHERE from_user_id = 1) UNION (SELECT * FROM user_relation WHERE to_user_id = 1) ) AS i ORDER BY trust_degree You have to assign an alias to your select. But in this case a UNION is not necessary and could be replaced by a simple OR , as @Karoly Horvath points out in his comment. The resulting query would look like this: SELECT * FROM user_relation WHERE from_user_id = 1 OR to_user_id = 1 ORDER BY trust_degree It is written in the documentation of UNION : To apply ORDER BY or LIMIT to an individual SELECT , place the clause inside the parentheses that enclose the SELECT : (SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10) UNION (SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10); ... Use of ORDER BY for individual SELECT statements implies nothing about the order in which the rows appear in the final result because UNION by default produces an unordered set of rows. ... ...

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 : $record = '123'; $this->db->distinct(); $this->db->select('accessid'); $this->db->where('record', $record); $query = $this->db->get('accesslog'); then $query->num_rows(); should go a long way towards it. You can also run ->select('DISTINCT `field`', FALSE) and the second parameter tells CI not to escape the first argument. With the second parameter as false , the output would be SELECT DISTINCT `field` instead of without the second parameter, SELECT `DISTINCT` `field` try it out with the following code function fun1() { $this->db->select('count(DISTINCT(accessid))'); $this->db->from('accesslog'); $this->db->where('record =','123'); $query=$this->db->get(); return $query->num_rows(); }

Answer : As of latest version of Sequelize (i.e. 3.3.2 ), authenticate can be used to check the connection: var sequelize = new Sequelize("db", "user", "pass"); sequelize.authenticate().then(function(errors) { console.log(errors) }); authenticate simply runs SELECT 1+1 AS result query to check the db connection. UPDATE : Errors by the newest API need to be handled in catch : sequelize .authenticate() .then(() => { console.log('Connection has been established successfully.'); }) .catch(err => { console.error('Unable to connect to the database:', err); }); UPDATE 2 : I haven't tested this, but its only logical that the same thing can be reached with async/await : try { await sequelize.authenticate() } catch (err) { console.error('Unable to connect to the database:', err) } You won't see errors, like password authentication errors, in .then . From the sequelize documentation here: ...

Answer : If you use "IF NOT EXISTS" then the dbdelta script will not upgrade your database with delta's appeared after the initial creation of the database. (assuming you want to re-use the same sql script) at least... that is what i think DISCLAIMER : I'm not a WordPress Guru, only a MySQL DBA If you want to user a different query, try this SELECT COUNT(1) FROM information_schema.tables WHERE table_schema='dbname' AND table_name='tbname'; It will either return 0 (if table does not exist) or 1 (if table does exist) Try this one: global $wpdb; $table_name = $wpdb->base_prefix.'custom_prices'; $query = $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $table_name ) ); if ( ! $wpdb->get_var( $query ) == $table_name ) { // go go }