New answers tagged

0

What you do is to a) take a mysqldump of your database: mysqldump --user my_user --password=<pass> --host=my_db schema_name > my_schema.sql b) then use a tool such as sed to substitute MyISAM for InnoDB: sed -i 's/ENGINE=MyISAM/ENGINE=InnoDB/g' my_schema.sql c) restore the dump on your 5.6 instance: mysql --user=my_user --password=<pass&...


2

If you're looking for an easy-to-use, open-sourced tool, checkout etlalchemy. You can carry out your SQL Server to MySQL database migration with 4 lines of Python code: To Install: pip install etlalchemy (On El Capitan you may have to run pip install --ignore-installed etlalchemy) To Run: from etlalchemy import ETLAlchemySource, ETLAlchemyTarget ...


0

You could use a cron job to connect to the existing database, make a backup of the table, select the rows you need to move (over one week old) disconnect from the sql server, connect to the new server, insert the rows you have previously selected using a loop, disconnect from the second server, reconnect to first server, and delete the rows you don't ...


1

This line seems to be causing the problem `date_art` datetime(6) DEFAULT NULL, This works `date_art` datetime DEFAULT NULL, Is the version of the database you exported from the same as you are importing to? http://stackoverflow.com/questions/2572209/why-doesnt-mysql-support-millisecond-microsecond-precision http://dev.mysql.com/doc/refman/5.6/en/...


1

This is one of the ways SELECT CASE WHEN `amount`='type 1' then `amount` end as `amount1`, CASE WHEN `amount`='type 2' then `amount` end as `amount2`, CASE WHEN `amount`='type 3' then `amount` end as `amount3` FROM db.tablename; Ex: SELECT CASE WHEN amount=1000 then amount end as amount1, CASE WHEN amount=2000 then amount end as amount2, CASE ...


2

how to change the file directory of your log and data files and what are the effects Can I just change the settings of the instance to point it to the new directory, detach the existing databases move the data files and attach it back? Please share what would be the best approach in doing this. Below are two scripts I've used for this purpose ...


2

The SQL would be like: insert into tab1 (category_id, created_at, title, promo, content) as select 3, date_art, title_art, short_art, full_art from tab2 The ID column will get it's value during the insert.


1

The doubling up is because of your nested parenthesis. Processing will start with the inner-most pair and work outwards. In this case the innermost replacement is 1 -> Apple meaning 11 -> Apple1 -> AppleApple. Reverse the nesting, starting with the longest source value and work outward. Better still, put the mappings in a separate table and join to ...


0

I had the same problem with the extra 4 bytes being added to the beginning of all of my files as well. Instead of using -N option in my bcp command, I changed it to -C RAW. When you do this, bcp will be prompted with the following questions: Enter the file storage type of field FileData [image]: Enter prefix-length of field FileData [4]: Enter length of ...


0

Why use a UI tool when the query is this short? INSERT INTO db1.users (id, name, phone) SELECT id, first_name, mobile FROM db2.users; Caveats: You probably should not copy id if it is AUTO_INCREMENT. I was guessing which direction you wanted to copy.


2

There is a new system keyspace called "system_schema", but there are only two tables in it: Ok something must have gone wrong in your upgrade, because that's not right. When I check my system_schema 3.4, I see this: [cqlsh 5.0.1 | Cassandra 3.4 | CQL spec 3.4.0 | Native protocol v4] Use HELP for help. aploetz@cqlsh> use system_schema ; aploetz@cqlsh:...


0

By using REPLACE alone, you can use like the below: SELECT REPLACE(REPLACE(REPLACE(REPLACE(column, '1', 'ABC'), '2', 'DEF'), '3', 'GHI'), '4', 'JKL') FROM table WHERE column IN ('1', '2', '3', '4') The replace should be nested on other, not separate by semi colon. More over in WHERE clause instead of the OR you can use IN


2

You can use CASE for what you're using the replace for. Also, I've modified the WHERE to be more streamlined; SELECT CASE WHEN column = '1' THEN 'ABC' WHEN column = '2' THEN 'DEF' WHEN column = '3' THEN 'GHI' WHEN column = '4' THEN 'JKL' END AS column FROM table WHERE column IN ('1','2','3','4') As a note, if column is an int then you don'...


0

You'd better use: SELECT CASE WHEN column = 1 THEN 'ABC' WHEN column = 2 THEN 'DEF' WHEN column = 3 THEN 'GHI' END AS column FROM table WHERE ...


0

A couple of transactions running every 2-3 minutes and a 60GB database is pretty light IMHO and shouldn't add much load to an existing instance unless it's at the edge of it's capacity. A couple of quick questions to take into account. You are just moving the database not creating a new instance on the "heavily used" machines correct? On the old ...


0

I'm sure that such approach is feasible. Technically Grid 11.2.0.4 is fully compatible with RDBMS 11.2.0.3. You just need to be accurate in some parameters. I believe that in a new 11.2.0.4 grid you won't use very specific "compatible.rdbms" or "compatible.asm" attributes' value ('11.2.0.4.0', e.g.) which might conflict with 11.2.0.3 database. Ideally you ...


2

How to migrate windows login in from one sql server to another sql server on another domain So whatever logins the database has in DMN1 cannot be used in DMN2 anymore. it would take a lot of time to sort out a lot of windows logins in DMN1, get their equivalent login in DMN2 and map them to the right databases. What would be an easier way of doing ...


1

If you migrate from RDBMS to NoSQL then you will also have problems. They are not interchangeable for all types of usage. When you start a project then just before you start the creation of your data objects you must choose the one that suits best to your needs. Changing later will nearly always cause problems as soon as you use more then the 'standard' ...


0

Yes you can. Use SQL synonyms https://msdn.microsoft.com/en-us/library/ms187552(v=sql.105).aspx By doing that you'll have a full control behind the curtains, but please fully document it and be consistent. It is a nightmare to troubleshoot databases with synonyms. As an alternative you can use dynamic SQL, it might be more transparent.



Top 50 recent answers are included