Tag Info

New answers tagged

0

try this SELECT @i:=@i+1 AS iterator, t.ID, t.Title ,t.LSCNo FROM (SELECT ID, Title, LSCNo FROM tutorials_tbl WHERE Lscno IN ( SELECT Lscno FROM tutorials_tbl GROUP BY Lscno HAVING COUNT(Lscno) > 1 )ORDER BY Lscno) AS t, (SELECT @i:=0) AS foo


1

I found an utility that does exactly what I need : SQLYog (Database Synchronization Wizard)


1

Try this one, excute at a time set variable and select statement. SET @Variable:=0; SELECT @Variable:=@Variable+1 AS AddNewColumn,ID,Title,Lscno FROM tutorials_tbl WHERE Lscno IN ( SELECT Lscno FROM tutorials_tbl GROUP BY Lscno HAVING COUNT(Lscno) > 1 )ORDER BY Lscno


0

You should add one of these two indexes: ALTER TABLE asset ADD INDEX product_id_status_expiry_date(product_id, status, expiry_date); This will help with the filtering. ALTER TABLE asset ADD INDEX expiry_date_serial_number(expiry_date, serial_number); This will help with the filesort. Only one of them can be used at a time, so add only the most ...


0

Memory is not instrumented in MySQL until version 5.7 (currently in development), so this does make your question a bit of a guessing game. I can see from inside InnoDB status, that it doesn't appear to be InnoDB consuming the memory (assuming you collected this from when the problem was occurring): Total memory allocated 26217103360; in additional pool ...


3

Maybe the problem is that the Workbench is running your statements one at a time without you being noticed, while JDBC exactly sends to the database what you exactly ask it to send. See this answer, where a parameter is used on the connection driver to allow many SQL statements over a single JDBC statement object.


1

As u told it possible to create another database(with different name) with in the server. try once in local db then you can go into production.


0

With MySQL 5.5... ./mysql_install_db --basedir=/backup/mysql-cbt/ --datadir=/backup/mysql-cbt/data --defaults-file=/backup/mysql-cbt/my.cnf --user=mysql Hmm... I did this a lot with 5.6 GA (the latest release last year) and found after (a lot) of trail and error, it would work with this. ./scripts/mysql_install_db ...


1

These two are actions to be performed, respectively, when the referenced record on the parent table changes its id and when it gets deleted. If you execute: UPDATE parent SET id = -1 WHERE id = 1; And there is at least one record on child with parent_id = 1, 1) will fail; in cases 2) and 3), all records with parent_id = 1 are updated to parent_id = -1. ...


2

1) means that if the parent is deleted, the child is also deleted (not a good idea IMHO - you should keep track of all data that's ever been in a database), although this can be done using triggers. You could also SET NULL. 2) means that if the parent primary key is changed, the child value will also change to reflect that - again IMHO, not a great idea. If ...


1

I found the problem. The slave was trying to connect to port 7 instead of port 3306 for some reason. I had to manually specify that as part of CHANGE MASTER TO command and add MASTER_PORT=3306 and it works perfectly now.


1

To get the basic data you need an aggregation: select 1 + floor(datediff(date, mind) / 7) as week, year(date) as year, count(*) as num from atable t cross join (select min(date) as mind from atable ) td group by 1 + floor(datediff(date, mind) / 7), year(date) You can extend this using variables to get the cumulative ...


1

What you want is called the cumulative sum, you can do something like: create table transactions (transactionid int, d date); insert into transactions (transactionid, d) values (1, '2014-08-04'),(2,'2014-08-05'), (3, '2014-08-18') , (4, '2014-08-18'), (5,'2014-08-20'); select x.y, x.w, count(1) from ( select distinct year(d) as y, ...


1

Maybe you can group it by the most recent Monday of the Date SELECT COUNT(1) Transactions,WEEK(monday) Week,YEAR(monday) Year ( SELECT (Date - INTERVAL WEEKDAY(Date) DAY) monday FROM mytable WHERE Date >= MAKEDATE(YEAR(NOW()),1) ) A GROUP BY YEAR(monday),WEEK(monday); NOTE: The WHERE clause in the subquery collects everything from the first of ...


2

Are you working with datetimes? Notice that If I ran it now I'd get for instance: August, 28 2014 13:57:36+0000 Which means that if my dates are at midnight I'd get the same with < and <= You should probably cast this into DATE if you are to work with dates


1

Update 14:02 You installed mysql-server-core-5.6 which partially failed or partially installed. The -explicit_defaults_for_timestamp is only for MySQL 5.6. Its probable that the partial installation of mysql-server-core-5.6 added this option. I am going to suggest now that you remove any trace of the mysql-server-core-5.6 and reinitalize the data ...


0

I have this line in my.cnf [pol@localhost mysql-5.6.19-linux-x86_64]$ grep expli my.cnf explicit-defaults-for-timestamp = TRUE I think that the my.cnf parameters should use hyphens and not underscores.


0

This is a question which gets asked regularly in some form or other here. Take a look at my reply to a similar one not so long ago. Basically, there are two main tools out there for this - either Percona's pt-online-schema-change (available here) or Shlomi Noach's oak-online-alter-table (from here). There are a couple of other possible solutions mentioned ...


0

That will work but it's a pity it won't provide the advantage of using the WITH clause, that is not to execute the same query several times (with complex queries could be reaaaally slow and very demanding for the database engine; I suffered it). I would suggest inserting each SELECT defined in the original WITH clause into its own temporary table, and use ...


0

If what you are asking is how to access a remote MySQL table from another MySQL server, that can be achieved with the FEDERATED engine (disabled by default in mysql 5.5) os with the CONNECT engine (only available on MariaDB 10). Here you have the documentation to create such tables in both cases: Creating a FEDERATED table using CONNECTION. You need to ...


0

Open up an Command Line Window as Administrator. Then, follow these steps STEP 01 : Shutdown MySQL net stop mysql STEP 02 : Get the MySQL 5.5 Data into the MySQL 5.6 Data Folder cd "C:\ProgramData\MySQL\MySQL Server 5.6" move data data_empty mkdir data cd "C:\ProgramData\MySQL\MySQL Server 5.6\data" xcopy /s "C:\ProgramData\MySQL\MySQL Server ...


1

MyISAM [and] InnoDB data and log files are binary-compatible on all platforms having the same floating-point number format. Source. You can copy freely between those two platforms if you maintain the same vendor and version. Obviously, the binary executables for 64 bits will not work on 32-bit.


9

You said I heard that we can retrieve the data by creating a new table same as the old table with storage engine as innodb or myisam Whoever told you that should have told you to enable binary logs as @jynus commented. The BLACKHOLE Storage Engine does not store data at all. It is a special storage engine used in very meticulous setups. EXAMPLE #1 : ...


3

Use UPDATE image_count = VALUES(image_count). VALUES() expects a name from the columns you are inserting into, not the alias in the query. The query should be: INSERT INTO countingTable (image_count, article_id) SELECT COUNT(article_id) AS sum, article_id FROM imageTable GROUP BY article_id -- I suppose you skipped that line? ON ...


0

You probably have: local-infile=1 loose-local-infile=1 In your [client] section on /etc/my.cnf , /etc/mysql/my.cnf or /home/karthick.g.s/.my.cnf or somewhere else. Change it to a [mysql] section so it doesn't affect mysqladmin. You can also do: $ mysqladmin --no-defaults -u root -p ping



Top 50 recent answers are included