Sunday, July 7, 2013

[how to] MySQL #1064 error on line 2

[how to] MySQL #1064 error on line 2

MySQL #1064 error on line 2

Posted: 07 Jul 2013 09:04 PM PDT

I'm quite new on learning SQL and I seem to be running into an issue with the following code:

CREATE TABLE `read`    (`order` NOT NULL UNIQUE AUTO_INCREMENT,    `title` VARCHAR(50) NOT NULL,    `url` VARCHAR(50) NOT NULL,    PRIMARY KEY(`title`,`url`)  )DEFAULT CHARSET=UTF8;

It's returning the following error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOT NULL UNIQUE AUTO_INCREMENT, title VARCHAR(50) NOT NULL, url VARCHAR(50' at line 2

oracle 11g control files error

Posted: 07 Jul 2013 05:08 PM PDT

i use oracle 11g r2 and when startup in sqlplus faced this problem

   ORA-00205: error in identifying control file, check alert log for more info

then i went to alert log file i seen these errors 

ORA-00210: cannot open the specified control file    ORA-00202: control file: '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/ora_control1'                                                                                          ORA-27037: unable to obtain file status

Query mysql database through memcache

Posted: 07 Jul 2013 02:16 PM PDT

I have been trying to use memcache with MySQL 5.6 but I can not figure out how to do this.

I have add an entry into innodb_memcache.containers so I can query the database through memcache.

What I would usually do with memcache is get the value for the key by doing this:

$value = $memcache->get($key);

but in mysql5.6 potentially there could be a lot of tables specified , so using PHP , how do I specify the table I am trying to query against using the memcache layer?.

MySQL Migration from Slave to Master

Posted: 07 Jul 2013 04:26 PM PDT

I recently had a problem with our server. It required us to install a new hard drive, cpanel, and setup the old hard drive as a slave. How can I migrate the mysql database from the slave drive to the master drive? The slave contains the tables with MYD, MYI, and frm files. The database is probably about 20G.

I'm assuming SSH is the best way to do this. I am not very familiar with SSH commands and cannot find out how to do this. I've already setup the new database (on the fresh install on the master drive) with the same name, users, and passwords.

mongos and monod have the same default port?

Posted: 07 Jul 2013 11:18 AM PDT

Is it true that by default mongos and monod uses the same port 27017?

I had to pick a different port for one of these two. For example --port 37017 for mongos.

Did anyone have the same confusion?

Thanks,

How to switch Quiescing to Normal Mode in Oracle?

Posted: 07 Jul 2013 10:40 AM PDT

I have two instance database Oracle, they are ORCL11 and ORCL10,.... I've configured them, it looks like correct. Then, I connect to replication user admin ORCL10 

conn repadmin/repadmin@orcl10

And run SQL command in replication admin user ORCL10,...

BEGIN DBMS_REPCAT.RESUME_MASTER_ACTIVITY (gname=>'scott_repg');end;/

Then, i connect to replication admin user ORCL11 to see gname status. 

conn repadmin/repadmin@orcl11;  select gname,status from dba_repgroup;

It display gname = SCOTT_REPG and status = NORMAL,.... but if connect to replication admin ORCL10

conn repadmin/repadmin@orcl10;  select gname,status from dba_repgroup;

It display that gname = SCOTT_REPG and STATUS = QUIESCING, I think it does not have any impact, so I test my replication on ORCL10, for example : 

conn system/ok@orcl10;  insert into DEPT values (101,'Financial');  select*from DEPT;

It works on table DEPT ORCL10 (1 row created), but when i switch to ORCL11 then run simple sql query :

conn system/ok@orcl11;  select*from DEPT;

it doesn't show anything, i've searched it on Google but there is nothing tutorial how to fix that, I think the problem is about how to switch QUIESCING to NORMAL mode at ORCL10. I've try this way to fix it : 

ALTER SYSTEM QUIESCE RESTRICTED;  ALTER SYSTEM UNQUIESCE;

But it still doesn't solve the problem,..... Would you help me to fix this problem ??? thanks !!!

Should I deploy Mongodb sharding for 50 collections?

Posted: 07 Jul 2013 05:19 PM PDT

I have a 10 node clusters that runs about 50 concurrent jobs. Each job needs to read/write a separate collection. So I have about 50 collections roughly. Each collection has about 20 M records. Most of the time, jobs only need to do sequential read/write.

For simplicity, I could deploy a single instance of mongodb that has no replication, no sharding. And have 50 separate collections. But the single node where mogodb is running becomes a hotspot and the rest 9 nodes can't share the read/write load.

So I would like to leverage the resource and balance the load. But I guess a 20M record collection is not worth sharding? Especially I only need to do sequential read/write. I thought about merging 50 collections into one big collection. But I am stumbled on the document size limitation which is 16 MB.

Any suggestions? Thanks,

How do I monitor bandwidth used by SQL Server on port 1433?

Posted: 07 Jul 2013 06:55 AM PDT

I have a single server running a .NET web application and a SQL Server database (2008 Standard). I'm planning to move the database onto a separate server but in order to provision the network hardware I'd like to benchmark the data throughput between the web application and the database. Can port 1433 be monitored internally? If so is there any tool native to Windows 2008 R2 that can do this, or would I need some 3rd party application like WireShark? My connection string is referencing the database using server=localhost, is it still possible to tap into 1433 to see the bandwidth used on this port?

Essentially I'm trying to determine if I need a Gbit or 100 Mbit connection between the web server and database server. Any thoughts on this would be much appreciated.

/usr/sbin/mysqld was deleted and replaced by another mysqld with different inode

Posted: 07 Jul 2013 07:50 PM PDT

This happened on three of our mysql server running on RHEL. While checking for open and deleted files, I found that mysqld in use is deleted (as seen in lsof) and was replaced by a similar mysqld (in /usr/sbin/mysqld) with a different inode. Size and blocks of both the files (deleted and current) are same. new mysqld (not in use) seems to be of the same version as the deleted one. I am trying to figure out what could have caused this (there are no cronjobs running on the system). I checked system logs, database logs and yum logs and found nothing relevant. Any input is appreciated.

Thanks!

creating a report on the mysql jasperReport server

Posted: 07 Jul 2013 09:50 AM PDT

I have mysql local database, in it I have sample data mart. I have installed jasperReport server to my computer. Just using this tool, I want connect to the local mysql database and just create a pdf report or any report without using any tool except jasperReport server. Can I do ? HOw?

Note; report will be directly constructed on the whole data reside in the mysql database. (No filtering) I want to see the report on the jasperReport report page. If any change occur on the mysql database, it should be carried out on the report page of the JAsperReport server

Oracle remove and place (re-install) database on 10g

Posted: 07 Jul 2013 05:49 AM PDT

I need to put together a plan to execute some change scripts to a Oracle 10g database, which will add some values, change some values a drop a table and create a new table.

I have a step for creating a DMP of the original database before the changes and for running a script that will execute all the changes. But I need a back-out plan if things go wrong. I was wondering what is the bets step(s) to drop the database and import the old DMP file?

I know about the sqlplus command:

imp <touser>/<password>@<host_address>/<service> file=<filename>.dmp fromuser=<fromuser> touser=<touser> log=backup_import.log

But what is the best way to drop the existing database? Is it just 

drop user <schema_user> cascade;

Thanks in advance.

K

SQL Agent embedded PowerShell script in CmdExec step fails with import-module sqlps

Posted: 07 Jul 2013 12:50 PM PDT

SQL Server 2008R2 PowerShell 2.1

I am trying to create a SQL Agent job that dynamically backs up all non-corrupted SSAS databases on an instance without the use of SSIS. In my SQL Agent job, when I create a CmdExec step and point to a PowerShell script file (.ps1) like this:

powershell.exe "c:\MyPSFile.ps1"

the job executes successfully (or at least gets far enough to only encounter logic or other syntax issues).

This approach won't work for a final solution, because there is a requirement to keep the PowerShell script internal to SQL. So I have a different CmdExec step that embeds the PowerShell script like so:

powershell.exe "import-module sqlps –DisableNameChecking    $server_name = "localhost"  $backup_location = "C:\BackupsGoHere"    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") | out-null  $server = New-Object Microsoft.AnalysisServices.Server  $server.connect($server_name)    # Generate an array of databases to be backed up  foreach ($database in ($server.get_Databases() | Where {$_.EstimatedSize -gt 0 -and $_.Cubes.Count -gt 0})) {      $directory_path = $backup_location + "\" + $database.Name      if (!(Test-Path -Path $directory_path)) {          New-Item $directory_path -type directory | out-null      }      [string] $timestamp = date      $timestamp = $timestamp.Replace(':','').Replace('/','-').Replace(' ','-')      $database.Backup("$directory_path\$database-$timestamp.abf")  }  $server.disconnect()"

However, when executed with the embedded script, the job errors out quickly with the following response:

The specified module 'sqlps' was not loaded because no valid module file was found in any module directory.

Why can't I reference the module from an embedded script, but doing so in a ps1 file works just fine?

MySQL error 2006 MySQL server has gone away after upgrade to 5.6.10 using homebrew on Max OS X 10.8.3

Posted: 07 Jul 2013 04:49 AM PDT

I upgraded my installation of MySQL on my Mac OS X 10.8.3 using homebrew

brew install mysql

Everything seemed to go smoothly, but...not really, as it turns out.

I cannot add a new user (neither through the command line nor through phpmyadmin. I always get the error

Error in processing request: #2006 MySQL server has gone away

I can create new databases without a problem, and I can install, for example Joomla 3.1.1 with no problems.

However, when I try to install a new extension into my Joomla installation, I get errors. For example, I get the following error when I try to install a component from NoNumber:

1062 Duplicate entry '1' for key 'PRIMARY' SQL=INSERT INTO `u5z87_extensions` (`name`,`type`,`element`,`folder`,`client_id`,`enabled`,`access`) VALUES ('plg_system_nnframework','plugin','nnframework','system','0','1','1')

Same thing for many other extensions.

I tried to start mysql with

mysql.server start --log-warnings=2

and it started with no problem

Starting MySQL   SUCCESS!

I looked up issues where error #2006 were being reported and how to solve them, and they referred to modifying /etc/my.cnf but there is no /etc/my.cnf configuration file on my system, and there wasn't one in the previous installation of MySQL 5.5.29 (also installed with homebrew) and I had no problems.

I ran brew doctor and the output is below

$ brew doctor  Warning: "config" scripts exist outside your system or Homebrew directories.  `./configure` scripts often look for *-config scripts to determine if  software packages are installed, and what additional flags to use when  compiling and linking.    Having additional scripts in your path can confuse software installed via  Homebrew if the config script overrides a system or Homebrew provided  script of the same name. We found the following "config" scripts:        /usr/local/opt/php54/bin/php-config

The output from brew --config is also below:

$ brew --config  HOMEBREW_VERSION: 0.9.4  ORIGIN: https://github.com/mxcl/homebrew  HEAD: 86ce842c700a47abdc354e80cc3b0b3938ab2132  HOMEBREW_PREFIX: /usr/local  HOMEBREW_CELLAR: /usr/local/Cellar  CPU: 8-core 64-bit nehalem  OS X: 10.8.3-x86_64  Xcode: 4.6.2  CLT: 4.6.0.0.1.1365549073  LLVM-GCC: build 2336  Clang: 4.2 build 425  X11: 2.7.4 => /opt/X11  System Ruby: 1.8.7-358  Perl: /usr/bin/perl  Python: /usr/bin/python  Ruby: /Users/alisamii/.rvm/rubies/ruby-1.9.3-p374/bin/ruby

Exploring a little further, I tried to revert to my previous install of MySQL using

brew switch mysql 5.5.29

This resulted in the following output:

$ brew switch mysql 5.5.29  Cleaning /usr/local/Cellar/mysql/5.5.29  Cleaning /usr/local/Cellar/mysql/5.6.10  135 links created for /usr/local/Cellar/mysql/5.5.29

I then accessed mysql from the command line and ran status:

mysql> status  --------------  mysql  Ver 14.14 Distrib 5.5.29, for osx10.8 (i386) using readline 5.1    Connection id:      13  Current database:  Current user:       alisamii@localhost  SSL:            Not in use  Current pager:      stdout  Using outfile:      ''  Using delimiter:        ;  Server version:     5.6.10 Source distribution  Protocol version:       10  Connection:         Localhost via UNIX socket  Server characterset:    utf8  Db     characterset:    utf8  Client characterset:    utf8  Conn.  characterset:    utf8  UNIX socket:        /tmp/mysql.sock  Uptime:         1 hour 56 min 24 sec

As you can see above, it reports mysql Ver 14.14 Distrib 5.5.29, for osx10.8 (i386) using readline 5.1

But, it also says Server version: 5.6.10 Source distribution

So something isn't syncing up.

Furthermore, when I show databases, I get the following output:

mysql> show databases      -> ;  +--------------------+  | Database           |  +--------------------+  | information_schema |  +--------------------+  1 row in set (0.00 sec)

But I know I have more than just an information_schema database. in phpMyAdmin, I have 14 databases displaying.

So, somehow, it seems to me that mysql is not connecting to the right server, as phpmyadmin reports the same server information that mysql's status command reports: Software version: 5.6.10 - Source distribution.

At this point, I am completely lost, and would really need some help.

When SQL Server uses Multi Page Allocations

Posted: 07 Jul 2013 06:49 PM PDT

What are the consumers of MPA (multi page allocations) in SQL Server?

I know data pages are always 8K. Is there a situation where data/index page make use of MPA? It makes sense that execution plans can use MPA as they can exceed 8 KB.

There is a blog here that suggests use of MPA but it refers to stored procedures (one with 500 parameters). In the attached screenshot I see an execution plan using around 11 MB; does this use MPA? Is there a way to confirm that memory allocation for the execution plan is using multi page allocation?

enter image description here

My confusion is what actually uses MPA (multi page allocator). The example in the link I posted shows a complex execution plan which will require contiguous allocation over 8 KB.

An answer below suggests that there are many things like linked server or extended stored procedures that can use MPA. Which for some reason I am not able to agree. For example Extended Stored procedures can be C++ code using HeapAlloc which SQL server has little control over to manage its memory (it has to be at Windows OS level).

Extended stored procedures even in SQL 2012 still consumes memory outside buffer pool so it has to nothing to do with multi page allocation. The same applies to Linked servers especially if you use third party providers (e.g. ORACLE).

Is the key_buffer_size applicable to myisam tmp tables?

Posted: 07 Jul 2013 04:50 PM PDT

I have a database about 750GB in size. It's all innodb.

Larger analytical queries often need to group by several columns or use distinct so it's common that MySQL will have to create tmp tables. The tmp tables fit into memory.

My cache hit ratio (Key_reads / Key_read_requests) is 0.

When MySQL creates these tmp tables, I'm guessing it doesn't it create pseudo indexes to be used by key_buffer_size.

Optimize UNION query in MySQL

Posted: 07 Jul 2013 09:03 PM PDT

I have a problem with a UNION query in MySQL. We have 10 millions players on our website and we would like to select players with a multi-criterias system. For exemple, selecting US people, men, more than 35 years of age.

We are using "vertical partionning": 1 table per criter. For example:

* user_country  - id_user  - id_country

We would like to do this kind of query:

SELECT id_inscri FROM userdata_langue  WHERE id_langue='43'    UNION  SELECT id_inscri FROM userdata_sexe  WHERE sexe='2'    UNION  SELECT id_inscri FROM userdata_nb_jour  WHERE nb_jour>='31'    UNION  SELECT id_inscri FROM userdata_last  WHERE last<='2013-04-07'    AND last>='2013-04-03' ;

How do I optimize that?

----- More details

Explain output of the query:

id  select_type table   type    possible_keys   key key_len ref rows    Extra  1   PRIMARY userdata_langue ref id_langue   id_langue   1   const       398846  Using index  2   UNION   userdata_sexe   ref sexe    sexe    1   const   1667137 Using index  3   UNION   userdata_nb_jour    range   nb_jour nb_jour 2   NULL    5830    Using where; Using index  4   UNION   userdata_last   range   last    last    3   NULL    371614  Using where; Using index  NULL    UNION RESULT    <union1,2,3,4>  ALL NULL    NULL    NULL    NULL    NULL

SHOW CREATE TABLE

Table   Create Table  userdata_langue CREATE TABLE `userdata_langue` (   `id_inscri` bigint(20) NOT NULL,   `id_langue` tinyint(3) unsigned NOT NULL,   PRIMARY KEY (`id_inscri`),   KEY `id_langue` (`id_langue`)  ) ENGINE=InnoDB DEFAULT CHARSET=latin1

Update one table from another table while sorting that table based on one column

Posted: 07 Jul 2013 03:50 PM PDT

This is the problem I'm trying to figure out in MySQL. We have an old table contains some forms submitted by our users. Somehow, the previous decision was each time a user comes to this survey, a new form will be submitted. So in the old table we easily have several rows with the same Firstname, Lastname, but different values in the other columns, and there's a timestamp column Date_Submission as well.

Now we are trying to move everything to a new table, but this time, for each person we only keep one row. And we want to keep some of the the latest old data of that user (like email, phone number, etc)

I could do the following:

update newtable, oldtable set newtable.email = oldtable.email where newtable.firstname = oldtable.firstname and newtable.lastname = oldtable.lastname;

Apparently this won't give me the "latest" old date for each person.

So I tried this one:

update newtable, oldtable set newtable.email = oldtable.email where newtable.firstname = oldtable.firstname and newtable.lastname = oldtable.lastname order by oldtable.Date_Submission;

But they MySQL will complain with:

"ERROR 1221 (HY000): Incorrect usage of UPDATE and ORDER BY".

So I'm wondering, what's the correct way to achieve this?

increasing mysql table open cache?

Posted: 07 Jul 2013 02:50 PM PDT

I often read that it is best to increase this variable slowly. Can someone explain why? My status indicates that I should increase it... What is best practice / "slowly"?

Thanks!

select count(*) in mysql 5.5 innodb-- rewrite advice?

Posted: 07 Jul 2013 11:50 AM PDT

I need advice on how to rewrite a select count(*) query for innodb tables mysql 5.5. in new environment its very slow... 

select count(*)   from mails3     join questions using (question_id)   where mails3.from_user_id = '86696'     and mails3.is_community_star = 1     and mails3.rating = 3     and questions.is_rated_community = 1;

the query execution plan looks simple enough but very slow

*************************** 1. row ***************************             id: 1    select_type: SIMPLE          table: mails3           type: ref  possible_keys: question_id_index,emails_doublestars,favorite_emails,user_responses,owner_responses,sentmail,watchlist,read_responses,rating_date,read_and_unrated            key: emails_doublestars        key_len: 8            ref: const,const,const           rows: 2784          Extra: Using where  *************************** 2. row ***************************             id: 1    select_type: SIMPLE          table: questions           type: eq_ref  possible_keys: PRIMARY,is_rated_community_senddate_idx            key: PRIMARY        key_len: 5            ref: answerology.mails3.QUESTION_ID           rows: 1          Extra: Using where  2 rows in set (0.00 sec)

Why does this procedure raise a privilege error?

Posted: 07 Jul 2013 08:50 AM PDT

I am having trouble getting the following procedure to run on a remote MySQL database - the given error is privilege based (#1227). Locally, the procedure runs fine.

QUESTIONS

  • Could somebody help me understand which specific part of this code raises this error, i.e. requires the said privilege?
  • Is there any way I could have corresponding functionality without a privileged call?

enter image description here

MySQL5.6 on Mac OS X 10.6.8 problems when granting permissions to root

Posted: 07 Jul 2013 10:50 AM PDT

I'm having serious problems with a MySQL 5.6 instance on a Mac Server.

We had to upgrade MySQL to a newer version, but it turned to be a bad idea, as we lost control to it. We had a backup of the /data directory and the my.cnf file as well. However, when setting an init_file to restore the previous password and its permissions. So we created a text file with this content:

UPDATE mysql.user SET Password=PASSWORD('myOriginalPassword') WHERE User='root';  FLUSH PRIVILEGES;

We have double checked the init_file permissions (we even gave it a chmod 777, to make sure it worked), but something is not working. When we run mysqld_safe --init_file=/usr/local/mysql/bin/init_file it tries to start the server, but then it shuts itself down. The log is as follows:

130308 13:23:52 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data  2013-03-08 13:23:53 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).  2013-03-08 13:23:53 18979 [Warning] Setting lower_case_table_names=2 because file system for /usr/local/mysql/data/ is case insensitive  2013-03-08 13:23:53 18979 [Note] Plugin 'FEDERATED' is disabled.  2013-03-08 13:23:53 18979 [Note] InnoDB: The InnoDB memory heap is disabled  2013-03-08 13:23:53 18979 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins  2013-03-08 13:23:53 18979 [Note] InnoDB: Compressed tables use zlib 1.2.3  2013-03-08 13:23:53 18979 [Note] InnoDB: CPU does not support crc32 instructions  2013-03-08 13:23:53 18979 [Note] InnoDB: Initializing buffer pool, size = 128.0M  2013-03-08 13:23:53 18979 [Note] InnoDB: Completed initialization of buffer pool  2013-03-08 13:23:53 18979 [Note] InnoDB: Highest supported file format is Barracuda.  2013-03-08 13:23:53 18979 [Note] InnoDB: 128 rollback segment(s) are active.  2013-03-08 13:23:53 18979 [Note] InnoDB: Waiting for purge to start  2013-03-08 13:23:53 18979 [Note] InnoDB: 1.2.10 started; log sequence number 19440939  2013-03-08 13:23:53 18979 [ERROR] /usr/local/mysql/bin/mysqld: unknown option '--skip-locking'  2013-03-08 13:23:53 18979 [ERROR] Aborting    2013-03-08 13:23:53 18979 [Note] Binlog end  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'partition'  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_SYS_TABLES'  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_FT_CONFIG'  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_FT_DELETED'  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_FT_INSERTED'  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_METRICS'  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_CMPMEM'  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_CMP_RESET'  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_CMP'  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_LOCKS'  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_TRX'  2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'InnoDB'  2013-03-08 13:23:53 18979 [Note] InnoDB: FTS optimize thread exiting.  2013-03-08 13:23:53 18979 [Note] InnoDB: Starting shutdown...  2013-03-08 13:23:54 18979 [Note] InnoDB: Shutdown completed; log sequence number 19440949  2013-03-08 13:23:54 18979 [Note] Shutting down plugin 'BLACKHOLE'  2013-03-08 13:23:54 18979 [Note] Shutting down plugin 'ARCHIVE'  2013-03-08 13:23:54 18979 [Note] Shutting down plugin 'MRG_MYISAM'  2013-03-08 13:23:54 18979 [Note] Shutting down plugin 'MyISAM'  2013-03-08 13:23:54 18979 [Note] Shutting down plugin 'MEMORY'  2013-03-08 13:23:54 18979 [Note] Shutting down plugin 'CSV'  2013-03-08 13:23:54 18979 [Note] Shutting down plugin 'sha256_password'  2013-03-08 13:23:54 18979 [Note] Shutting down plugin 'mysql_old_password'  2013-03-08 13:23:54 18979 [Note] Shutting down plugin 'mysql_native_password'  2013-03-08 13:23:54 18979 [Note] Shutting down plugin 'binlog'  2013-03-08 13:23:54 18979 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete    130308 13:23:54 mysqld_safe mysqld from pid file /usr/local/mysql/data/server.myServerName.com.pid ended

The problem may definitely come from the [Error] lines, but as we haven't used the --skip-locking, it's just confusing. If we try to log into mysql using mysql -r -p and set the password we used, it can't connect to the server, as it is not started. Any ideas where this problem may come from?

Importing data with a lot of SELECTS during transaction

Posted: 07 Jul 2013 01:50 PM PDT

I'm using SQL server 2008 R2 and I have a products table with an auto increment integer id column as primary key and a product_no column (unique) and 6 tables like articles and product_assets which have foreign keys to the products table.

I need to import about 1 million products into the products table and the other tables (about 16 million rows in total (all tables together))

This import should run as quickly as possible (<=6h) and I need to perform additional SELECTs per data row from the tables during import to validate the data. The data source is a CSV file so I'm making a transaction for each line which represents an article (entry for the articles table) due to the fact that the import could fail at any line.

During import it should also be possible to read data from the mentioned tables with a different connection.

Currently this import runs in over 24h (the SELECTs to validate the data which I have to do for every line from the CSV during import takes it toll)

Any advice how to improve import performance in such a case?

I can split the CSV data in an preprocessing step into parts (e.g. last char of the product number) so that the parts don't interfere on row level and run these parts in different threads simultaneously if that could improve the import speed somehow. I mention this because the import machine and the sql server machine have a low CPU utilization during import so i guess a lot of time is wasted with network I/O (machines are connected with gigabit ethernet).

PostgreSQL replication for archiving

Posted: 07 Jul 2013 06:49 AM PDT

I've looked around a bit and haven't found a very clear strategy or method for what I'm trying to do, which is surprising because I would think it would be a fairly common practice.

I currently use Bucardo to replicate in a Master Slave setup. This works fine except I want the Slave to retain all records ever written to it. The master is a smaller system which will need to have its tables truncated periodically. I don't want these truncates to be carried over to the Slave. I would think this would be a fairly common practice but have been stumped finding a solution that will work.

Could anyone point me in the right direction? The right direction doesn't necessarily need to involve using Bucardo either.

Thanks

Replicate RDS-MySQL to a non-amazon host?

Posted: 07 Jul 2013 02:37 PM PDT

I have a fairly large MySQL (5.1) database running in Amazon as an RDS instance. I'm working on migrating it out of Amazon. To do that smoothly I'd like to set up the new hardware as a read-only replica of the RDS instance.

I know RDS supports replication within RDS. Is there any way to configure it to replicate to a host which is not in RDS?

How to prevent high memory, CPU and time consumption by MySQL restore?

Posted: 07 Jul 2013 07:50 AM PDT

I have a local test machine with 2 GB RAM and a dual core processor.

I imported a dump on that using

mysql -uuser -psecret < script.sql

script.sql is a 700 MB file which contains a single database with more than 100 tables.

Restore took approximately 25 minutes.

Question: Why it took so long time? I have already optimized my server settings. The dump also contains extended inserts and disables keys). Is 700 MB is too large for this kind of machine? mysqld was using 100% of one of the CPU when I checked using htop and 22% of the memory.

Now what actually I want is to convert all my tables of that database to InnoDB. So for that I executed a simple shell command:

mysql -uuser -psecret -Bse "select Concat('Alter Table ' , TABLE_SCHEMA ,'.',TABLE_NAME , ' Engine= \'InnoDB\' ;') from information_schema.tables where ENGINE='myisam' and TABLE_SCHEMA='db_name';" > Innodb.sql

so Innodb.sql contains the script for converting tables to InnoDB.

Now when I run Innodb.sql one of my table takes more than 20 minutes for conversion. It contains only 1,378,397 records.

In total it takes more than 30 minutes. In the meantime memory usage by mysqld daemon was 73%. The CPU usage was OK at this time.

How can I minimize the time consumtion? Should I go for changing MySQL server settings or anything else?

If anybody wants my my.cnf setting I will share that.

How do I find my current SCN?

Posted: 07 Jul 2013 11:44 AM PDT

Given any version of Oracle:

  • How do I find my current SCN?
  • What is the maximum possible SCN?

How can I profile SQL Azure?

Posted: 07 Jul 2013 02:30 PM PDT

I am writing a web site that uses SQL Azure heavily. However, it is painfully slow.

Is there an easy way to profile the live SQL Azure instance?
Posted by at No comments:

[SQL 2012] beginner:)

[SQL 2012] beginner:)

beginner:)

Posted: 06 Jul 2013 08:04 AM PDT

I would like to know if anyone has any suggestions on which version of SQL Server 2012 a beginner might injstall on a PC to begin learning SQL. I tried importing a simple contact Database from Excel and Express was giving me all kinds of fits, any other recommendations/thanks:-)
Posted by at No comments:

[T-SQL] Different between two query

[T-SQL] Different between two query

Different between two query

Posted: 06 Jul 2013 08:43 PM PDT

Hi Dear,What is different between two query?query 1:[code="sql"]SELECT dbo.Person.CodeFROM dbo.Person LEFT OUTER JOIN dbo.CustomerSegment ON dbo.Person.CustomerSegment = dbo.CustomerSegment.Code INNER JOIN dbo.CustomerCreditD ON dbo.CustomerSegment.Code = dbo.CustomerCreditD.CustomerSegment LEFT OUTER JOIN dbo.CustomerCreditException ON dbo.CustomerCreditException.Person = dbo.Person.CodeWHERE (dbo.Person.Code = 22122738)[/code]query 2:[code="sql"]SELECT dbo.Person.CodeFROM dbo.CustomerCreditD INNER JOIN dbo.CustomerSegment ON dbo.CustomerCreditD.CustomerSegment = dbo.CustomerSegment.Code RIGHT OUTER JOIN dbo.Person LEFT OUTER JOIN dbo.CustomerCreditException ON dbo.CustomerCreditException.Person = dbo.Person.Code ON dbo.CustomerSegment.Code = dbo.Person.CustomerSegmentWHERE (dbo.Person.Code = 22122738)[/code]Thanks.

Shrink database goes to suspended

Posted: 06 Jul 2013 03:54 AM PDT

Hi friends,I have sql server 2008 R2 enterprise database. I have compressed tables and then created indexes for the same table. Then compressed the same indexes on that table. When am trying to shrink that database, the session is continously in suspended state. After 2 days even 100 MB is not shrunk. Now the available free space of the database is 600 GB. Kindly guide me how to get rid off this issue.Thanks in advance.
Posted by at No comments:

[SQL Server 2008 issues] Simple select query with no where or join takes a long time

[SQL Server 2008 issues] Simple select query with no where or join takes a long time

Simple select query with no where or join takes a long time

Posted: 06 Jul 2013 07:23 PM PDT

Do you guys have any idea why a simple select statement without any joins or where clause would take over 9 minutes to run? The table holds under 300,000 records and isn't used for anything yet.Table:[code="plain"]CREATE TABLE [dbo].[TEMP_RSS_PRODUCTS2]( [product_name] [varchar](500) NULL, [noride_add] [varchar](70) NULL, [manufacturer_name] [varchar](50) NULL, [product_longdesc] [nvarchar](4000) NULL, [pno] [varchar](100) NOT NULL, [product_no] [int] NOT NULL, [main_part_no] [varchar](50) NULL, [mfr_pno] [varchar](50) NULL, [price] [smallmoney] NULL, [product_id] [varchar](70) NULL, [weight] [float] NULL, [width] [float] NULL, [height] [float] NULL, [depth] [float] NULL, [est_min_shipping] [smallmoney] NULL, [dropship_fee] [smallmoney] NULL, [qualifies_for_free_shipping] [bit] NULL, [status] [varchar](4) NULL, [upc_code] [varchar](30) NULL, [qty_available] [int] NULL, [vehicle_specific] [bit] NULL, [pic] [varchar](255) NULL, [option_desc] [varchar](60) NULL, [ship_zip] [char](5) NULL, [keywords] [varchar](1024) NULL, [retail_price] [smallmoney] NULL, [main_product_name] [varchar](300) NULL, [gender] [char](1) NULL, [age] [char](5) NULL, [color] [varchar](50) NULL, [material] [varchar](50) NULL, [viscosity] [varchar](20) NULL, [size] [varchar](30) NULL, [size_unit] [varchar](15) NULL, [sale_price] [smallmoney] NULL, [sale_end_dt] [smalldatetime] NULL, [pattern] [varchar](50) NULL, [num_options] [int] NULL, [p_upc] [varchar](30) NULL, [p_isbn] [varchar](30) NULL, [option_id] [uniqueidentifier] NULL) ON [PRIMARY]GOSET ANSI_PADDING OFFGOCREATE NONCLUSTERED INDEX [IX_TEMP_RSS_PRODUCTS2_pno] ON [dbo].[TEMP_RSS_PRODUCTS2] ( [pno] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]GOCREATE NONCLUSTERED INDEX [IX_TEMP_RSS_PRODUCTS2_product_no] ON [dbo].[TEMP_RSS_PRODUCTS2] ( [product_no] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]GO[/code]Query selects all columns except the last 4:[code="plain"]select product_name, noride_add, manufacturer_name, product_longdesc, pno, product_no, main_part_no, mfr_pno, price, product_id, [weight], width, height, depth, est_min_shipping, dropship_fee, qualifies_for_free_shipping, [status], upc_code, qty_available, vehicle_specific, pic, option_desc, ship_zip, keywords, retail_price, main_product_name, gender, age, color, material, viscosity, size, size_unit, sale_price, sale_end_dt, patternfrom dbo.TEMP_RSS_PRODUCTS2[/code]Execution plan says:SELECT Cost: 0%Table Scan Cost: 100%Thanks for any help.

Service pack 2 SQL Server 2008 R2 is released

Posted: 06 Jul 2013 10:05 AM PDT

http://support.microsoft.com/kb/2630458*never mind, old news. rss acting up

How to load dynamically created excel into DB table (SQL Server 2012) using SSIS

Posted: 05 Jul 2013 09:46 PM PDT

Hi All,I need to load excel sheet data into database table in SQL Server (2012) using SSIS (2012). The columns of the excel sheet are more than 800 and they are not static. The excel sheet will be provided at monthly basis with different number of columns and number and nature of the columns will vary each month.For example in July 2013, there are 850 columns in the excel sheet and in August 2013, it can be expected 900 columns in the excel sheet. We have to import the excel sheet into the database table. The target table will be created each time before loading the table from excel sheet. The columns of the target table will be determined by reading the header columns in the excel sheet.Please help me out to get the solution for the above problem.

Please HELP !! :SQL SERVER with MySQL Linked Server. Error Executing Trigger

Posted: 06 Jul 2013 11:42 AM PDT

Hi sirs ! If somebody can help me i would appreciate a lot !I have SQL server with MySQL Linked server through ODBC.Also have a Trigger on SQL Server Table :[i]ALTER TRIGGER [dbo].[TriggerStock] ON [dbo].[T_Contabilidad] FOR UPDATE,insertASBEGINDECLARE @Id integer, @Stock integer SELECT @ID = Idt_Conta,@stock=Stock FROM INSERTED IF @Stock>0 BEGIN UPDATE OPENQUERY(MYSQLVINC,'SELECT Idt_web, disponible FROM mibasedatos.t_web where idt_web=@id') SET disponible= 1 END ELSE BEGIN UPDATE OPENQUERY(MYSQLVINC,'SELECT Idt_web, disponible FROM mibasedatos.t_web where idt_web=@id') SET disponible= 0 ENDEND[/i]The idea : I NEED TO UPDATE MySQL TABLE WHEN SQL SERVER Table is UPDATED/INSERTPROBLEM : SQL throws an error :[b]Mens 7390, Nivel 16, Estado 2, Procedimiento TriggerStock, Línea 18No se pudo realizar la operación solicitada porque el proveedor OLE DB "MSDASQL" del servidor vinculado "MYSQLVINC" no admite la interfaz de transacción requerida[/b].(In english : Could'nt do the operation because of OLE DB MSDASQL from linked server does'nt admit transation required interface.Any help please ? Thanks in advance

Ranking Based of Advert Breaks

Posted: 06 Jul 2013 11:43 AM PDT

Hi People,This is a tough one for me. Let me start by explaining what i want to do then posting my code and data for you guys to give me ideas on how i can go about it.I have this table that shows date, time, programme code(i.e. brandflag field) and station that an advert is being captured:[code="sql"]CREATE TABLE [dbo].[TestTable]( [AdDate] [datetime] NULL, [AdTime] [nvarchar](8) NULL, [FK_StationId] [nvarchar](5) NULL, [BrandFLag] [nvarchar](5) NULL) ON [PRIMARY]GOINSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:46:36', N'A1', N'T79')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'20:20:00', N'A1', N'T1004')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'20:43:00', N'A1', N'T1004')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'20:58:22', N'A1', N'T0')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:10:57', N'A1', N'T21')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:12:22', N'A1', N'T21')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:30:51', N'A1', N'T21')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:31:00', N'A1', N'T21')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:32:00', N'A1', N'T21')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:33:00', N'A1', N'T21')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:34:00', N'A1', N'T21')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:34:01', N'A1', N'T21')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:02:57', N'A1', N'T320')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:03:00', N'A1', N'T320')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:03:23', N'A1', N'T320')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:07:59', N'A1', N'T320')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:08:34', N'A1', N'T320')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:15:00', N'A1', N'T320')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:15:01', N'A1', N'T320')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:15:02', N'A1', N'T320')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'23:35:00', N'A1', N'T0')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'06:30:16', N'A10', N'T79')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:00:00', N'A10', N'T0')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:30:05', N'A10', N'T79')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:31:30', N'A10', N'T79')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:32:27', N'A10', N'T79')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:45:45', N'A10', N'T79')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:46:10', N'A10', N'T79')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:48:12', N'A10', N'T79')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'09:01:18', N'A10', N'T0')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'09:02:18', N'A10', N'T1442')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'09:30:31', N'A10', N'T1443')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'10:01:00', N'A10', N'T0')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'15:58:05', N'A10', N'T0')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'16:03:00', N'A10', N'T202')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'16:59:02', N'A10', N'T0')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'16:59:14', N'A10', N'T0')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'17:00:13', N'A10', N'T1154')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'17:31:15', N'A10', N'T219')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'18:00:00', N'A10', N'T1154')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'18:30:37', N'A10', N'T231')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'18:58:40', N'A10', N'T0')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'18:59:47', N'A10', N'T0')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'19:00:00', N'A10', N'T375')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'19:17:46', N'A10', N'T1164')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'19:21:53', N'A10', N'T1164')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'19:31:42', N'A10', N'T1154')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'19:58:22', N'A10', N'T0')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'20:00:00', N'A10', N'T0')INSERT [dbo].[TestTable] ([AdDate], [AdTime], [FK_StationId], [BrandFLag]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'20:01:53', N'A10', N'T0')[/code]And i have this code that shows the position of each advert per each programme (brandflag):[code="sql"]SELECT AdDate, AdTime, FK_StationId,brandflag, CAST(rank() over(partition by brandflag order by fk_stationid, addate,adtime) AS VARCHAR(10)) + '/ ' + CAST(count(*) over(partition by fk_stationid,brandflag) AS VARCHAR(10)) as PositionFROM dbo.testtableGROUP BY AdDate, AdTime, FK_StationId, brandflagHAVING (AdDate = CONVERT(DATETIME, '2013-01-04 00:00:00', 102))order by fk_stationid, addate, adtime[/code]That gives me this result:[code="sql"]CREATE TABLE [dbo].[TestResult]( [AdDate] [datetime] NULL, [AdTime] [nvarchar](8) NULL, [FK_StationId] [nvarchar](5) NULL, [brandflag] [nvarchar](5) NULL, [Position] [varchar](22) NULL) ON [PRIMARY]GOSET ANSI_PADDING OFFGOINSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:46:36', N'A1', N'T79', N'1/ 1')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'20:20:00', N'A1', N'T1004', N'1/ 2')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'20:43:00', N'A1', N'T1004', N'2/ 2')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'20:58:22', N'A1', N'T0', N'1/ 2')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:10:57', N'A1', N'T21', N'1/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:12:22', N'A1', N'T21', N'2/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:30:51', N'A1', N'T21', N'3/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:31:00', N'A1', N'T21', N'4/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:32:00', N'A1', N'T21', N'5/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:33:00', N'A1', N'T21', N'6/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:34:00', N'A1', N'T21', N'7/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:34:01', N'A1', N'T21', N'8/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:02:57', N'A1', N'T320', N'1/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:03:00', N'A1', N'T320', N'2/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:03:23', N'A1', N'T320', N'3/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:07:59', N'A1', N'T320', N'4/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:08:34', N'A1', N'T320', N'5/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:15:00', N'A1', N'T320', N'6/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:15:01', N'A1', N'T320', N'7/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'22:15:02', N'A1', N'T320', N'8/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'23:35:00', N'A1', N'T0', N'2/ 2')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'06:30:16', N'A10', N'T79', N'2/ 7')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:00:00', N'A10', N'T0', N'3/ 11')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:30:05', N'A10', N'T79', N'3/ 7')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:31:30', N'A10', N'T79', N'4/ 7')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:32:27', N'A10', N'T79', N'5/ 7')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:45:45', N'A10', N'T79', N'6/ 7')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:46:10', N'A10', N'T79', N'7/ 7')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'07:48:12', N'A10', N'T79', N'8/ 7')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'09:01:18', N'A10', N'T0', N'4/ 11')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'09:02:18', N'A10', N'T1442', N'1/ 1')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'09:30:31', N'A10', N'T1443', N'1/ 1')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'10:01:00', N'A10', N'T0', N'5/ 11')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'15:58:05', N'A10', N'T0', N'6/ 11')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'16:03:00', N'A10', N'T202', N'1/ 1')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'16:59:02', N'A10', N'T0', N'7/ 11')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'16:59:14', N'A10', N'T0', N'8/ 11')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'17:00:13', N'A10', N'T1154', N'1/ 3')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'17:31:15', N'A10', N'T219', N'1/ 1')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'18:00:00', N'A10', N'T1154', N'2/ 3')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'18:30:37', N'A10', N'T231', N'1/ 1')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'18:58:40', N'A10', N'T0', N'9/ 11')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'18:59:47', N'A10', N'T0', N'10/ 11')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'19:00:00', N'A10', N'T375', N'1/ 1')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'19:17:46', N'A10', N'T1164', N'1/ 2')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'19:21:53', N'A10', N'T1164', N'2/ 2')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'19:31:42', N'A10', N'T1154', N'3/ 3')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'19:58:22', N'A10', N'T0', N'11/ 11')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'20:00:00', N'A10', N'T0', N'12/ 11')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'20:01:53', N'A10', N'T0', N'13/ 11')[/code]Actually, the kind of result i'm looking for is something like breaking the results into separate advert breaks, e.g. when you have a programme, one way of determining the advert breaks in the programme is to look at the closeness of the adverts e.g: looking at the results below, you will notice that the first record i.e '1/8' and '2/8' are close together having the times '21:10:57' and '21:12:22', but for the rest of the timing they start from '21:30:51' through to '21:34:01', i would consider that as the 2nd advert break and consider the first set as the first advert break.I would like to get a result displays in this format e.g. '1/2 of 1' meaning that for the first advert break, the first advert is advert 1 of 2 of the first advert break i.e ('21:10:57') and ('21:12:22') is advert '2/2 of 1' i.e breaking the adverts basedon advert breaks.Then the rest will follow suit e.g. ('21:30:51') will be '1/6 of 2' meaning advert 1 of six adverts in the 2nd break.[code="sql"]INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:10:57', N'A1', N'T21', N'1/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:12:22', N'A1', N'T21', N'2/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:30:51', N'A1', N'T21', N'3/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:31:00', N'A1', N'T21', N'4/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:32:00', N'A1', N'T21', N'5/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:33:00', N'A1', N'T21', N'6/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:34:00', N'A1', N'T21', N'7/ 8')INSERT [dbo].[TestResult] ([AdDate], [AdTime], [FK_StationId], [brandflag], [Position]) VALUES (CAST(0x0000A13C00000000 AS DateTime), N'21:34:01', N'A1', N'T21', N'8/ 8')[/code]Please if you need any clarification, i will be willing to explain again, i hope there is a solution to this.Thanks very much.Tim

Differential restore without full backup restore!!

Posted: 06 Jul 2013 06:53 AM PDT

HII have 2 servers. I do a full backup of serverA and restore it to ServerB with NORECOVERY.Now I do a differential backup of ServerA after 1 hr and Apply it to ServerB with RECOVERY. So my ServerB become replica of ServerA. Now going forward, I just want to do differential backups of ServerA and apply them onServerB. To achieve this, Do I always need to do a full database restore of ServerA with NORECOVERY and then Apply differential with RECOVERY? Full backup size is big , around 50GB and it takes time to do it everytime. Is there a way just to apply the differential without first restoring the full DB?

for me problem this select insert

Posted: 05 Jul 2013 08:59 PM PDT

create table table1(phone int,kod_name int ,kod_fname int,kod_country int,kod_auto int) -- create table table2 (name_ nvarchar(30),kod int) insert into table2 values('james',1)insert into table2 values('stivens',2)insert into table2 values('carlos',3)create table table3 (f_name nvarchar(30),kod int) insert into table2 values('john',1)insert into table2 values('tayson',2)insert into table2 values('swarzneger',3)create table table4 (country nvarchar(30),kod int) insert into table2 values('argentina',1)insert into table2 values('brazilia',2)insert into table2 values('korea',3)create table table5 (m_auto nvarchar(30),kod int) insert into table2 values('mersedec',1)insert into table2 values('jaguar',2)insert into table2 values('landrover',3)how i make created procedure for insert to table1 when name=james then in kod_name_d insert 1 (kod table2)when fname=jhon then in kod_fname insert 1 (kod table3 )when country=korea then in kod_country insert 3 (kod table4 )when auto=jaguar then in kod_auto insert 2 (kod table5 )forexamle i inserted table 1 (11111,'james','jhon','korea','jaguar')after inserted table1table1-----------phone kod_name kod_fname kod_country kod_auto ----- -------- --------- ----------- --------11111 1 1 3 2

last insert date

Posted: 05 Jul 2013 09:27 PM PDT

Hi,My question: i have database emit046 here i need all table last insert/update/deleted date time information required. please help.........thanks in advance
Posted by at No comments:
Subscribe to: Posts (Atom)

Search This Blog