How do I configure a MySQL Cluster with a redundant mysqld host? this is my first post here, so go easy on me.

I'm trying to rebuild my data architecture (which is currently a set of one primary MySQL server and a redundant fallback; the tables are primarily InnoDB for transactional features). This has worked well in the past, but with recent increases in traffic, it's not holding up very well.

At first I was considering adding a 'memory' cache table to speed things up, but I'd prefer something with transactions. So I've been looking into setting up a MySQL Cluster, but the documentation is a lot to take in, and I've now gotten myself very confused. Here's what I hope to accomplish:

I'd still like to have two mysqld machines, but I'd like to add two or three ndb nodes, and have the mysqld machines double as ndm-mgmd.

Is this a practical setup? How do I go about configuring this? If anyone knows of helpful examples or guides, I'd be pleased as punch (I've been searching, and each guide I stumble upon is twice as confusing as the last).

A great place to get started with MySQL Cluster are these Quick Start guides:
http://mysql.com/products/cluster/get-started.html#quickstart

In terms of hardware profile, commodity servers are just fine - take a look at some of the recent benchmarks - 4.3m QPS across 8 x Intel dual socket servers with 24GB RAM:
http://mysql.com/why-mysql/benchmarks/mysql-cluster/

Hope this helps.  Let us know how you get along, and also go to the MySQL Cluster forum for direct access to engineering team: forums.mysql.com/list.php?25


Multiple databases or one large database? My company has a framework where different areas of a region are created as separated databases - we call them datamarts. All of the databases are exactly the same and in some regions we have 20 -30 databases where some or all are included in a particular query. Is there a performance penalty when we perform queries that join these databases or does SQL Server join these cross database queries seamlessly. Some of the databases may have a few million transactions.

Do the data marts need to have any of different schemas or different backup and recovery strategies? These would justify the separate databases.

Having these data marts has disadvantages:

query performance degradation
logical inconsistency

difficulty to enforce certain constraints between data-marts (unique, FK)
duplication of lookup data in each data mart, followed by drift of values

(globally) inconsistent restores
operational overhead
HA/DR

Your post does not provide the reasoning why data marts were chosen, so I cannot judge what advantages were leveraged or expected.

For a more thorough discussion see Multi-Tenant Data Architecture.

If you want performant queries on aggregated data from all data-marts, why not do just that? Aggregate the data-marts into a single DW, powered by columnstores, and enjoy performance.


BobMgr::GetBuf: Sort Big Output Buffer write not complete after 60 seconds One of our production servers showing below error msg in Sql Server Log from last week,

  SQL Server has encountered 20674 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [D:\Databasefile.mdf] in database [Database] (11). The OS file handle is 0x0000000000000828. The offset of the latest long I/O is: 0x00001c3bde0000
  and today in addition to the above one more msg,

  BobMgr::GetBuf: Sort Big Output Buffer write not complete after 60 seconds.

is this the indication of a serious issue? How can we resolve this?

BobMgr::GetBuf: Sort Big Output Buffer write not complete after 60 seconds.

Big Output Buffers (BoBs) are used when a sort spills to tempdb.

The 'big' buffer optimizes for large sequential I/O instead of writing sort records individually.

A delay of this length indicates a problem with the disk subsystem.


How to run DB2 export command in shell I am trying to run the following DB2 command through the Python pyodbc module:

DB2 export to C:\file.ixf of ixf select * from emp_hc

I am successfully connected to the DSN using the pyodbc module in Python and it works fine for select statements, but when I try to execute the following command from the Python IDLE 3.3.2:

cursor.execute(" export to ? of ixf select * from emp_hc",r"C:\file.ixf")

I get an error:

  pyodbc.ProgrammingError: ('42601', '[42601] [IBM][CLI
  Driver][DB2/LINUXX8664] SQL0104N An unexpected token "db2 export to ?
  of" was found following "BEGIN-OF-STATEMENT". Expected tokens may
  include: "". SQLSTATE=42601\r\n (-104) (SQLExecDirectW)')

When I use

cursor.execute(" export to C:\file.ixf of ixf select * from emp_hc")

I get another error:

  Traceback (most recent call last): File "", line 1, in cursor.execute("export to C:\myfile.ixf of ixf select * from emp_hc") pyodbc.ProgrammingError: ('42601', '[42601] [IBM][CLI Driver][DB2/LINUXX8664] SQL0007N The character "\" following "export to C:" is not valid. SQLSTATE=42601\r\n (-7) (SQLExecDirectW)')

Am I doing something wrong? Any help will be greatly appreciated.

From what I came to know DB2 export is a command run in shell, not through SQL via ODBC.

can you please give me some more information on how to run the command in the shell i am confused and what does that mean ? any guide or small quick tutorial will be great

There are three (well, actually four...) ways to execute export in DB2.

There is the db2export API (.sh in Unix/Linux, .exe in Windows). This can be executed from anything that understands command shell.

There is the export command to the db2 command. This can be run from a DB2 interactive session, or from the command shell.

There is also the export command through the ADMIN_CMD stored procedure. This allows you to invoke the export from a stored procedure which is accessable through anything that can execute SQL against DB2. This is probably the option you are looking for if you want to do export via SQL through another tool.

There is also the db2move utility which can perform an export, but that is done via command shell as well.


What exactly are iblog files in mysql I would like to understand these ibdata files as these play vital role in the crash recovery procedure. I could not find proper resources over the web for this.

ibdata1

The file ibdata1 is the system tablespace for the InnoDB infrastructure.

It contains several classes for information vital for InnoDB

Table Data Pages
Table Index Pages
Data Dictionary
MVCC Control Data

Undo Space
Rollback Segments

Double Write Buffer (Pages Written in the Background to avoid OS caching)
Insert Buffer (Changes to Secondary Indexes)

Click Here to see a Pictorial Representation

You can divorce Data and Index Pages from ibdata1 by enabling innodb_file_per_table. This will cause any newly created InnoDB table to store data and index pages in an external .ibd file.

Example

datadir is /var/lib/mysql
CREATE TABLE mydb.mytable (...) ENGINE=InnoDB;, creates /var/lib/mysql/mydb/mytable.frm

innodb_file_per_table enabled, Data/Index Pages Stored in /var/lib/mysql/mydb/mytable.ibd
innodb_file_per_table disabled, Data/Index Pages Stored in ibdata1

No matter where the InnoDB table is stored, InnoDB's functionality requires looking for table metadata and storing and retrieving MVCC info to support ACID compliance and Transaction Isolation.

Here are my past articles on separating table data and indexes from ibdata1

Oct 29, 2010 : My Original Post in StackOverflow
Nov 26, 2011 : ERROR 1114 (HY000) at line 6308 in file & The table user_analysis is full
Feb 03, 2012 : Scheduled optimization of tables in MySQL InnoDB
Mar 25, 2012 : Why does InnoDB store all databases in one file?
Apr 01, 2012 : Is innodb_file_per_table advisable?

iblog files (a.k.a. ib_logfile0, ib_logfile1)

If you want to know what the ib_logfile0 and ib_logfile1 are for, they are the InnoDB Redo Logs. They should never be erased or resized until a full normal shutdown of mysqld has taken place. If mysqld ever crashes, just start up mysqld. It will read across ib_logfile0 and ib_logfile1 to check for any data changes that were not posted to the the double write buffer in ibdata1. It will replay (redo) those changes. Once they are replayed and stored, mysqld becomes ready for new DB Connections.


Business logic in stored proc vs middle layer (c#) This is more of an architecture question. I once worked at a financial company that had a web application.

Front end javascript.
Middle layer was WebAPI to access the backend SQL Server.
Back End SQL Server database.

The company put all its business logic in stored procedures. After talking with other companies, I began to hear that most put their business logic in the middle layer.

I once asked someone at the financial company why was there such a big use of stored procs but didn't get a good answer. Recently, at a job interview, they asked what the advantages and disadvantages were of putting all the business logic in the database vs the middle layer. I didn't have a good answer.

Can anyone provide some thoughts on why this is or is not a good idea?

A) Scale  The middle tier can be scaled easily - hence the web farm concept. Scaling out the DB tier is much more difficult. While some products can do this it is not yet trivial and mainstream.

B) Cost Typically web servers are common-or-garden boxes. DB servers, however, tend to be larger, more complex and more resilient. This all translates to "expensive." A recent employer estimated a CPU tick on the DB was ten times more expensive than one on an application server.

C) Reuse Logic embodied in a stored procedure cannot simply be linked into, say, a stand-alone mobile app. Changing the SP affects every application which uses that DB, whether that application is ready for the change or not.

D) Reuse Logic embodied in a stored procedure is common to all applications which use the DB. Programmers cannot side-step the rules at a whim. Changing the SP affects every application which uses that DB, ensuring consistency across the enterprise.

E) Tooling There are more, and more sophisticated, languages, tools and techniques available for development in the application tier than there are in the database (from comments, with thanks).

F) Network traffic Typically a business function will require many reads and / or writes. Often the output of one statement will be the input to a following statement. If SQL statements are held in the application each will require a network round-trip to the server. If the SQL is held in a stored procedure there will be a single network trip, sending the parameters in and receiving only the final result back; the network cost of the intermediate results is avoided.


Access denied Error in MySQL and Drupal We are trying to deploy a Drupal application on our production server.

We see this strange error Lost connection to MySQL server at ''Access denied root@ipaddres password(yes) after deploying.

We made all necessary corrections in settings.php and also give GRANTs to the webserver to access the database.  The webserver and the database server are physically two different machines.

We are able to connect to the database server from our web server through the command line.

There is no firewall enabled on either server.

Any update we make to the database means the site goes down and displays an ACCESS DENIED error.

Can any one guide me to resolve this issue?

Thanks

May be lame to do this but worthy to double check the username and password and ip. "Copy paste the exact connect string in commandline"
Triage more by only keeping DB connection from app/web server and check the DB connectivity  functionality and see if the issue repeats. If yes, do the same from someother host with same app/web interface server version. [with this we can be sure that the host is no problem] .


Which Disk a Device Lives on? I would like to know which Disk a database device is being stored.  When I use "format" command in SCO Unix command line, it shows that the system has two disks (Disk 0 and Disk 1).  They are:

0. c0t0d0 <IBM-DNES-309170-SA30 cyl 11195 alt 2 hd 5 sec 320>
   /iommu@f,e0000000/sbus@f,e0001000/espdma@f,400000/esp@f,800000/sd@0,0
1. c0t3d0 <SEAGATE-ST34520N-1206 cyl 9004 alt 2 hd 4 sec 246>
   /iommu@f,e0000000/sbus@f,e0001000/espdma@f,400000/esp@f,800000/sd@3,0

When I check my database using sp_helpdb, I find that its data is in "sybdbs" and its transaction log is in "syblogs".  And then when I use sp_helpdevice to check, I find these:

device_name physical_name
----------- ------------------
sybdbs      /dev/rdsk/c0t0d0s1
syblogs     /dev/rdsk/c0t3d0s4

I have used "df /dev/rdsk" command and "df -F ufs" command and confirm that they are in local disks, not in network shared folders.

I would like to know which Disk that /dev/rdsk/c0t0d0s1 is on, and which Disk that /dev/rdsk/c0t3d0s4 is on.  How do I do this?

The best thing to do to resolve your question is to run the lsblk command. Without any arguments, it gives a great tree view of the system's block devices (see below). Check the man page (lsblk(8)).

Then, use df -h on your file of interest (in this example, it's the MySQL server). df -ah gives more information - in this case, the mount point and the filesystem.

[pol@localhost inst]$ df -ah ./bin/mysqld
Filesystem               Size  Used Avail Use% Mounted on
/dev/mapper/fedora-home   17G   16G  189M  99% /home
[pol@localhost inst]$

So, this tells us that mysqld is mounted on /home on the logical volume /dev/mapper/fedora-home. By checking the output of lsblk, it can be seen (check below) that it's on the sd5 partition of the sda device.

[EDIT - I've added new commands - and I'm giving this question a +1, since I've learnt a truckload trying to answer all of its possiblities! Check out the threads here (1, 2)]

[EDIT2]

Doing yet more exploring, I found these two threads (1, 2) - if you really want to get "down and dirty" with your file system :-). I won't list the output of all the commands found in them - just show some of the interesting ones (hopefully from your perspective). Search for the section also marked [EDIT2] below.

[EDIT3]

Check the end of this post for information I could find on SunOS/Solaris. Search for the section marked [EDIT3].

===============================

Sample output of df -h Note that without specifying the file, it gives the result for the entire operating system, but not the entire computer. I also have a Windows OS on the same machine, which does appear with the lsblk command.

[pol@localhost inst]$ df -h
Filesystem               Size  Used Avail Use% Mounted on
/dev/mapper/fedora-root   34G   31G 1015M  97% /
devtmpfs                 994M     0  994M   0% /dev
tmpfs                   1002M   12M  990M   2% /dev/shm
tmpfs                   1002M  9.0M  993M   1% /run
tmpfs                   1002M     0 1002M   0% /sys/fs/cgroup
tmpfs                   1002M  308K 1001M   1% /tmp
/dev/mapper/fedora-home   17G   16G  193M  99% /home
/dev/sda3                477M  141M  307M  32% /boot
/dev/sdc1                466G   53G  414G  12% /run/media/pol/INTENSO
[pol@localhost inst]$ df -h /home
Filesystem               Size  Used Avail Use% Mounted on
/dev/mapper/fedora-home   17G   16G  193M  99% /home
[pol@localhost inst]$ df -h /bin
Filesystem               Size  Used Avail Use% Mounted on
/dev/mapper/fedora-root   34G   31G 1015M  97% /
[pol@localhost inst]$

So, in your particular case, you want:

`df -h  /dev/rdsk/c0t0d0s1`

and

`df -h  /dev/rdsk/c0t3d0s4`

For logical volumes, use the lvs command - as detailled in this post for more info.

i.e.

sudo lvs -o +devices /dev/rdsk/c0t3d0s4 &c.

Sample on my CentOS system:

sudo lvs -o +devices /dev/mapper/fedora-root
[sudo] password for pol:
  LV   VG     Attr       LSize  Pool Origin Data%  Move Log Cpy%Sync Convert Devices
  root fedora -wi-ao---- 34.06g                                              /dev/sda5(4773)
[pol@localhost inst]$

So, /dev/mapper/fedora-root is on sda5.

On further investigation, I also found this thread and found 3 futher commands.

You could also look at the output of

`sudo fdisk -l`

and

`more /proc/diskstats`

And lshw -class disk -short or (verbose) lshw -class disk

Sample of lsblk output (really nice tree output!):

[pol@localhost inst]$ lsblk
NAME                          MAJ:MIN RM   SIZE RO TYPE MOUNTPOINT
sda                             8:0    0 111.8G  0 disk
├─sda1                          8:1    0   100M  0 part
├─sda2                          8:2    0  58.5G  0 part
├─sda3                          8:3    0   500M  0 part /boot
├─sda4                          8:4    0     1K  0 part
└─sda5                          8:5    0  52.7G  0 part
  ├─fedora-swap               253:0    0     2G  0 lvm  [SWAP]
  ├─fedora-root               253:1    0  34.1G  0 lvm  /
  └─fedora-home               253:2    0  16.6G  0 lvm  /home
sdc                             8:32   0 465.8G  0 disk
└─sdc1                          8:33   0 465.8G  0 part /run/media/pol/INTENSO
sr0                            11:0    1   1.5G  0 rom
sr1                            11:1    1    97M  0 rom
loop0                           7:0    0   100G  0 loop
└─docker-253:1-1064987-pool   253:3    0   100G  0 dm
  └─docker-253:1-1064987-base 253:4    0    10G  0 dm
loop1                           7:1    0     2G  0 loop
└─docker-253:1-1064987-pool   253:3    0   100G  0 dm
  └─docker-253:1-1064987-base 253:4    0    10G  0 dm
[pol@localhost inst]$

Sample of sudo fdisk -l:

[pol@localhost inst]$ sudo fdisk -l

Disk /dev/sda: 111.8 GiB, 120034123776 bytes, 234441648 sectors
Units: sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disklabel type: dos
Disk identifier: 0x000bbe80

Device    Boot     Start       End   Blocks  Id System
/dev/sda1 *         2048    206847   102400   7 HPFS/NTFS/exFAT
/dev/sda2         206848 122882047 61337600   7 HPFS/NTFS/exFAT
/dev/sda3      122882048 123906047   512000  83 Linux
/dev/sda4      123906048 234441647 55267800   5 Extended
/dev/sda5      123908096 234440703 55266304  8e Linux LVM

Disk /dev/mapper/fedora-swap: 2 GiB, 2164260864 bytes, 4227072 sectors
Units: sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes

Disk /dev/mapper/fedora-root: 34.1 GiB, 36570136576 bytes, 71426048 sectors
Units: sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes

Disk /dev/mapper/fedora-home: 16.6 GiB, 17855152128 bytes, 34873344 sectors
Units: sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes

Disk /dev/sdc: 465.8 GiB, 500107859968 bytes, 976773164 sectors
Units: sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disklabel type: dos
Disk identifier: 0xf29eb7a1

Device    Boot Start       End    Blocks  Id System
/dev/sdc1       2048 976768064 488383008+  c W95 FAT32 (LBA)

Disk /dev/mapper/docker-253:1-1064987-pool: 100 GiB, 107374182400 bytes, 209715200 sectors
Units: sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 65536 bytes

Disk /dev/mapper/docker-253:1-1064987-base: 10 GiB, 10737418240 bytes, 20971520 sectors
Units: sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 65536 bytes
[pol@localhost inst]$

and sample of more /proc/diskstats:

[pol@localhost datamodeller]$ more /proc/diskstats
  11       0 sr0 29 0 232 4629 0 0 0 0 0 4629 4629
   8       0 sda 410458 263717 20392196 10898025 1245895 706584 24393828 59601990 0 10815598 70583361
   8       1 sda1 117 110 1816 3037 0 0 0 0 0 2773 3037
   8       2 sda2 111 110 1768 2632 0 0 0 0 0 2528 2632
   8       3 sda3 246 563 3636 6071 7 1 28 275 0 4779 6346
   8       4 sda4 6 0 30 156 0 0 0 0 0 156 156
   8       5 sda5 409862 262815 20383066 10884215 1066584 706583 24393800 52959483 0 5001674 63942507
 253       0 dm-0 147434 0 1179472 2314861 233219 0 1865752 675534980 0 437014 677849853
 253       1 dm-1 402913 0 16674730 12727464 543716 0 7478728 27512250 0 3889832 40239738
 253       2 dm-2 126146 0 2528176 2158551 1064161 0 15049320 37801569 0 7863269 39960160
  11       1 sr1 0 0 0 0 0 0 0 0 0 0 0
   8      16 sdb 0 0 0 0 0 0 0 0 0 0 0
   8      32 sdc 4698 120153 459862 36086 3 0 3 4 0 12517 36087
   8      33 sdc1 4633 119985 458930 32188 3 0 3 4 0 10451 32189
   7       0 loop0 0 0 0 0 0 0 0 0 0 0 0
   7       1 loop1 0 0 0 0 0 0 0 0 0 0 0
 253       3 dm-3 4412 0 100098 19616 88640 0 1041112 849625 0 46499 869245
 253       4 dm-4 367 0 2936 11243 74438 0 595472 166950100 0 22800 166961349
[pol@localhost datamodeller]$

Sample of sudo lshw -class disk -short:

[pol@localhost inst]$ sudo lshw -class disk -short
H/W path                 Device         Class          Description
==================================================================
/0/100/1d.7/1/2/0.0.0    /dev/sdc       disk           500GB External USB 3.0
/0/100/1d.7/1/3/0        /dev/sr1       disk           Mass Storage
/0/100/1d.7/1/3/0/0      /dev/sr1       disk
/0/100/1d.7/1/3/1        /dev/sdb       disk           SCSI Disk
/0/1/0.0.0               /dev/cdrom     disk           DVD-RAM UJ-850S
/0/1/0.0.0/0             /dev/cdrom     disk
/0/2/0.0.0               /dev/sda       disk           120GB TOSHIBA MK1234GS

Or a bit more verbose:

[pol@localhost inst]$ sudo lshw -class disk
  *-disk
       description: SCSI Disk
       product: External USB 3.0
       vendor: Intenso
       physical id: 0.0.0
       bus info: scsi@67:0.0.0
       logical name: /dev/sdc
       version: 0101
       serial: E1407473
       size: 465GiB (500GB)
       capabilities: partitioned partitioned:dos
       configuration: ansiversion=6 logicalsectorsize=512 sectorsize=512 signature=f29eb7a1
  *-cdrom
       description: SCSI CD-ROM
       product: Mass Storage
       vendor: HUAWEI
       physical id: 0
       bus info: scsi@65:0.0.0
       logical name: /dev/sr1
       version: 2.31
       capabilities: removable audio
       configuration: ansiversion=2 status=ready
     *-medium
          physical id: 0
          logical name: /dev/sr1
          capabilities: partitioned partitioned:mac
  *-disk
       description: SCSI Disk
       physical id: 1
       bus info: scsi@66:0.0.0
       logical name: /dev/sdb
       configuration: logicalsectorsize=512 sectorsize=512
  *-cdrom
       description: DVD-RAM writer
       product: DVD-RAM UJ-850S
       vendor: MATSHITA
       physical id: 0.0.0
       bus info: scsi@0:0.0.0
       logical name: /dev/cdrom
       logical name: /dev/sr0
       version: 1.20
       capabilities: removable audio cd-r cd-rw dvd dvd-r dvd-ram
       configuration: ansiversion=5 status=ready
     *-medium
          physical id: 0
          logical name: /dev/cdrom
  *-disk
       description: ATA Disk
       product: TOSHIBA MK1234GS
       vendor: Toshiba
       physical id: 0.0.0
       bus info: scsi@2:0.0.0
       logical name: /dev/sda
       version: AH00
       serial: X6PHF4EUS
       size: 111GiB (120GB)
       capabilities: partitioned partitioned:dos
       configuration: ansiversion=5 logicalsectorsize=512 sectorsize=512 signature=000bbe80
[pol@localhost inst]$

Doing yet more exploring, I found these two threads (1, 2) - if you really want to get "down and dirty" with your file system :-). I won't list the output of all the commands found in them - just show some of the interesting ones (hopefully from your perspective).

[EDIT2].

filefrag - report on file fragmentation.

stat - display file or file system status.

pvs - report information about physical volumes.

dmsetup - low level logical volume management.

For RAID devices, you might want to look at md/mdmon/mdadm /proc/mdstat.
md - Multiple Device driver aka Linux Software RAID.

[EDIT3]

From here and here, it appears that SCO Unix has very little to do with SunOS 5/Solaris. I assume that you are in fact using Solaris of some sort - SunOS hasn't been released under that name for more than 2 decades.

When you say "database device" - do you mean the data files or the server software itself? What RDBMS are you using? Maybe your RDBMS's documentation could help in this regard? And/or newsgroups/lists specialising in that particular server?

I'm afraid I don't have a working version of Solaris to hand (soon - I mean to challenge myself! :-)). I Googled "Solaris which disk is a file on" and found several links which might be useful. Also, check out @mustaccio 's post and this link in it.

I do mean to get into ZFS (are you using that?) and all of this (horribly complex) disk config stuff in "proper" Unix :-), but that's all the help I can be at the moment I'm afraid.


my root account has been deleted for unknown reason, how to recover? i have a program called heidiSQL on my computer that enables me to edit data inside my database with querys. recently tried to create a new user through this program while signed in as the root account. i used the following code

  create user 'john91'@'localhost' identified by 'google';
  grant all privileges on *.* to 'john91'@'localhost;
  flush privileges;

i then closed the HeidiSQL program and opened it again to sign in with the new user 'john91', it worked fine, but what struck me was that i couldn't sign in to my root account anymore.

i then open the command line and sign in with the user 'john91' (which works) and checked all my user in the server by using this code:

  select host,user from mysql.user;

i found that 'john91' was the only user in the system. i was able to create a new root account by using this code and while signed in with 'john91' user:

  create user 'root'@'127.0.0.1' identified as 'google';

this worked fine but i was unable to grant this root account any privileges, so when i used:

  grant all privileges on *.* to 'root'@'127.0.0.1';
  ERROR 1045 (28000): access denied for user 'john91'@'localhost' (using
  password: YES)

so is this the right way to recreate my root account, if yes, what am i doing wrong, if no, how should i do it?

Update, i need to create 'root'@'localhost' MySQL is not letting me do that through the user 'john91', how can it be done?

You were supposed to do this:

grant all privileges on *.* to 'john91'@'localhost' with grant option;

You forgot to give 'john91'@'localhost' grant option. Thus, you cannot grant privileges to anyone else.

You can fix that by doing the following

update mysql.user set grant_priv='Y' where user='john91' and host='localhost';
flush privileges;

Then, you should be able to run

grant all privileges on *.* to 'root'@'127.0.0.1';

Give it a Try !!!


Distributing MySQL database on n systems I have a webserver in my application which talks to a single MySQL node.  Instead of having just one MySQL node, I want to have a cluster of n nodes which form my database.

Is it possible to do this?  Can some one point to a resource where I can read about how to do this.

I read about MySQL cluster but am more confused now; is there any beginner tutorial for that?

I don't want to rewrite the application, as I am under the impression that application code should never know whether it's talking to one DB node or a cluster of DB Nodes.

This technique is called Sharding.

MySQL doesn't support this kind of partitioning therefore is necessary to implement sharding logic manually.

I think Spock Proxy, for example, it's a good starting point to think in this direction

Hope this helps


ON DUPLICATE KEY UPDATE question I'm trying to compare data from two systems. The basic idea is:

Create Temp Table.
Insert Table1 Data.
Insert Table2 data (Table a linked, remote server) with UNIQUE Ids respected.
Filter where first or second table has different info.

I'm trying to get the second set of inserts, from the second system, to insert, and if duplicate, update instead. I can't seem to get it right. For the final insert, what should I have?

I don't have the "Filter Part" because I don't have the Table2 data inserted. The filter part would show rows where Active, Last or First name is different between the two systems. Simple where Table1LastName <> Table2LastName type statements.

Note: I have this working with 2 temp tables created... and a full outer join and some "Where ID is null". I'm just trying to get this to work in a manner I haven't used before.

if object_id('tempdb..#CompareDrs') is not null
    drop table #CompareDrs;
GO

create table #CompareDrs
(
     ID varchar(20) unique
    ,TABLE1Active varchar(1)
    ,TABLE1LastName varchar(50)
    ,TABLE1FirstName varchar(50)
    ,TABLE2Active varchar(1)
    ,TABLE2LastName varchar(50)
    ,TABLE2FirstName varchar(50)
);
GO

INSERT INTO #CompareDrs (ID, TABLE1Active, TABLE1LastName, TABLE1FirstName)
    SELECT
         right('00000000000000000000' + ForeignID, 20) ID
        ,case when Active = 0 then 'I'
              when Active = 1 then 'A'
              else ' '
         end       TABLE1Active
        ,LastName  TABLE1LastName
        ,FirstName TABLE1FirstName
    FROM
                  [DATABASE1].dbo.DRs
    Order By
        ID;
GO

INSERT Into #CompareDrs (ID, TABLE2Active, TABLE2LastName, TABLE2FirstName)
    SELECT
         right('00000000000000000000' + Cast(DRDR# as Varchar(50)), 20) ID
        ,DRAFLG TABLE2Active
        ,DRLNAM TABLE2LastName
        ,DR1NAM TABLE2FirstName
    FROM
        [LINKEDERVER].[DATABASE].[CATALOG].[TABLE] DR
ON DUPLICATE KEY UPDATE TABLE2Active = VALUES(TABLE2Active),
                        TABLE2LastName = VALUES(TABLE2LastName),
                        TABLE2FirstName = VALUES(TABLE2FirstName),
GO

SELECT * FROM #CompareDrs;

If you're using SQL 2008, look at the MERGE statement, it can do what other platforms call UPSERT, UPDATE if it exists, otherwise INSERT.


Failed to create instance: Standalone DB Instances are not supported for Amazon RDS Aurora Question

What is the cause of Failed to create instance: Standalone DB Instances are not supported for Amazon RDS Aurora? Run the ansible script to create RDS Aurora but getting the error. If "aurora" is changed to "MySQL", no issue.

Ansible Script (Role)

- rds:
    region:          "{{ ec2_region }}"
    zone:             "{{ ec2_zone }}"
    command: create
    instance_name: new-database
    db_engine: "aurora"
    size: 20
    instance_type: db.t2.small
    username: admin
    password: 1nsecure

Error Message

fatal: [localhost]: FAILED! => {
    "changed": false,
    "failed": true,
    "invocation": {
        "module_args": {
            "apply_immediately": false,
            "aws_access_key": null,
            "aws_secret_key": null,
            "backup_retention": null,
            "backup_window": null,
            "character_set_name": null,
            "command": "create",
            "db_engine": "aurora",
            "db_name": null,
            "ec2_url": null,
            "engine_version": null,
            "force_failover": false,
            "instance_name": "new-database",
            "instance_type": "db.t2.small",
            "iops": null,
            "license_model": null,
            "maint_window": null,
            "multi_zone": null,
            "new_instance_name": null,
            "option_group": null,
            "parameter_group": null,
            "password": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER",
            "port": 3306,
            "profile": null,
            "publicly_accessible": null,
            "region": "ap-southeast-2",
            "security_groups": null,
            "security_token": null,
            "size": "20",
            "snapshot": null,
            "source_instance": null,
            "subnet": null,
            "tags": null,
            "upgrade": false,
            "username": "mysql_admin",
            "validate_certs": true,
            "vpc_security_groups": null,
            "wait": false,
            "wait_timeout": 300,
            "zone": "ap-southeast-2a"
        }
    },
    "msg": "Failed to create instance: Standalone DB Instances are not supported for the specified engine"
}

Research

Add RDS support for dbengine Aurora #3180 tells below but not sure how to add the cluster in RDS Ansible.

  fatal: [localhost]: FAILED! => {"changed": false, "failed": true, "msg": "Failed to create instance: Standalone DB Instances are not supported for the specified engine"}

  It's because you need to have an Aurora cluster created before you try and create the Aurora instance and you must pass the cluster name as one of the arguments when calling the create_db_instance API action.

Aurora databases use the database cluster API rather than the database API.

This means in terraform for instance you need to create them via the rds_cluster and rds_cluster_instance resources.

In ansible using this module might work: https://github.com/manicminer/ansible-modules-rds-cluster


Properly pivot an XML column with XQuery I would like to pivot the attributes and values of an SQL XML column and display it in an ordered columns and rows format.
To get started, this is what the XML column looks like in SQL:
<Model xmlns:dsc="ModelDescriptive" xmlns:draw="ModelDraw" xmlns:display="Display" xmlns:thd="Model3D">
  <dsc:Model name="15DE">
    <dsc:Defs>
      <dsc:Materials>
        <dsc:Material ref="E-HD-AnoAno" baseRef="E-HD" type="rod" productionGroup="OPTION" />
        <dsc:Material ref="E-3L44A-CLEAR" baseRef="E-3L44A" type="piece" productionGroup="OPTION" />
        <dsc:Material ref="R-TH01" baseRef="R-TH-TH" type="rod" productionGroup="ASSEMBLY" />
        <dsc:Material ref="M-TH05" baseRef="M-TH" type="meter" productionGroup="MOLDING" />
      </dsc:Materials>
    </dsc:Defs>
  </dsc:Model>
</Model>

…and here is my query :
 SELECT
      x.y.value('local-name(..)', 'VARCHAR(MAX)') ParentElementName
     ,x.y.value('local-name(.)', 'VARCHAR(MAX)') Attribut
     ,x.y.value('.', 'VARCHAR(MAX)') Value
     ,Row_number() Over(Partition by x.y.value('local-name(..)', 'VARCHAR(MAX)') order by x.y.value('local-name(..)', 'VARCHAR(MAX)') ASC)  as rn
 FROM @xmlDescriptive.nodes('//*[text()], //@*') AS x(y)
 WHERE x.y.value('local-name(..)', 'VARCHAR(MAX)')= 'Material'
 ORDER BY x.y.value('local-name(..)', 'VARCHAR(MAX)')

I get an interesting result using the Nodes method of XQuery and filtering only on the material from the bill of material (BOM).
However, I would like to present the data in a standardized fashion.
The first result I get looks like this:

Then I transform this result to rotate it using the Pivot method. Here is my method of trying to pivot my information:
 SELECT [ref], [baseRef], [type], [productionGroup]
 FROM
 (
     SELECT
          x.y.value('local-name(..)', 'VARCHAR(MAX)') ParentElementName
         ,x.y.value('local-name(.)', 'VARCHAR(MAX)') Attribut
         ,x.y.value('.', 'VARCHAR(MAX)') Valeur
         ,CAST(Row_number() Over(Partition by x.y.value('local-name(.)', 'VARCHAR(MAX)') order by x.y.value('local-name(..)', 'VARCHAR(MAX)') ASC) as varchar(10)) as rn
     FROM @xmlDescriptive.nodes('//@*') AS x(y)
     WHERE x.y.value('local-name(..)', 'VARCHAR(MAX)')= 'Material'

 ) d
 pivot
 (
   max(Valeur)
   FOR Attribut in ([ref], [baseRef], [type], [productionGroup])
 ) piv

And I find my result in are unordered. The column ref does not match my baseRef, type and productGroup columns:

I am a little confused. I would like to have my result formatted this way but ordered. What is wrong with my reasoning?
I would like my result to be presented like this:

I'm probably missing the point of your question but here is what I would do to get the result you are looking for.
with xmlnamespaces('ModelDescriptive' as dsc)
select M.X.value('@ref', 'varchar(max)') as ref,
       M.X.value('@baseRef', 'varchar(max)') as baseRef,
       M.X.value('@type', 'varchar(max)') as type,
       M.X.value('@productionGroup', 'varchar(max)') as productionGroup
from @xmlDescriptive.nodes('//dsc:Material') as M(X);

Your use of local-name() and //*[text()], //@*' suggests you want some kind of generic pivot but you use Material and the attribute names hard coded in the query so I don't know why you went down that road.


Error While restoring backup in SSMS When I tried to restore a backup an error occurred showing access denied. The error message is as follows:

  Restore failed for Server 'VAIO\sqlexpress'.
   (Microsoft.SqlServer.SmoExtended)

  System.Data.SqlClient.SqlError: The operating system returned the
  error '5(Access is denied.)' while attempting
  'RestoreContainer::ValidateTargetForCreation' on 'C:\Program
  Files\Microsoft SQL
  Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\ihrd_mvk_newspaperboy.mdf'.
  (Microsoft.SqlServer.Smo).

Open "SQL Server Configuration manager"=>set "SQL Server" logon as=>Local System.


TM REQUEST Rollback SQL Server 2012 Always on Availability Group I have continuous transactions with a TM REQUEST command in sp_who2 active which is at a state of ROLLBACK.

This is on an always on a SQL Server 2012 availability group.

Has anyone had experience of this before?

According to this SO thread Session hanging with TM REQUEST and plan handle 0x0000 it indicates transaction manager servicing a DTC request which isn't supported on AGs (because if a failover occurs at that time it can kill your database - as documented by Microsoft but without instructions to reproduce it so we can go into more detail).

You can confirm this by opening the DTC manager program and seeing what the numbers look like.


Oracle: How can I create a where clause programmatically? Suppose I have a table like:
TBL_CONDITIONS
| ID | WHERE_CLAUSE                          |
|----|---------------------------------------|
| 1  | fruit = 'apple'                       |
| 2  | delicious = 'T'                       |
| 3  | fruit = 'apple' AND gluten_free = 'T' |
| 4  | delicious = 'F'                       |

I'd like to be able to select the where clause on this table based on user input.  Say the user selected "3". The stored procedure is written thusly:
BEGIN
    SELECT * FROM SAMPLE WHERE (SELECT WHERE_CLAUSE
                  FROM TBL_CONDITIONS WHERE ID = 3);
END;

Unfortunately, this doesn't work.  Is there a way to run this type of query?  Or another way to accomplish the same task?

PL/SQL Dynamic SQL

Below is a simple example:

set serveroutput on

declare
  l_condition varchar2(100);
  l_rc sys_refcursor;
  l_record sample%rowtype;
begin
  select where_clause into l_condition from tbl_conditions where id = 3;
  open l_rc for 'select * from sample where ' || l_condition;
  loop
    fetch l_rc into l_record;
    exit when l_rc%notfound;
    dbms_output.put_line(l_record.column1 || ', ' || l_record.column2);
  end loop;
end;
/


MS SQL - Create trigger to prevent insert if it doesn't match specific parameters Suppose I have two tables

payments with columns payment_id, batch_id
and batches with columns batch_id, batch_type

Can I add a trigger to prevent inserts into the payments table when the batch_id being inserted is not a specific batch_type?

In short, for the inputted batch_id I want the trigger to check in the batches table to see if that batch_id has batch_type P.

Something similar was discussed here but for MySQL (I am looking for a MS SQL trigger ) :- How to make a trigger that will compare input with a value of other table?.

Thanks for your help!

I think something like this would solve your problem.  If a payment being inserted has a batch_id that points to a batches row where the batch_type <> 'P', the transaction is rolled back.

CREATE TRIGGER [dbo].[Payments_PreventBatchThatAreNotP] ON [dbo].[Payments]
AFTER INSERT
AS
SET NOCOUNT ON;

IF EXISTS (
        SELECT *
        FROM inserted p
        JOIN dbo.Batches b ON b.batch_id = p.batch_id
            AND b.batch_type <> 'P'
        )
BEGIN
    RAISERROR ('Batch type not equal "P" - transaction rolled back',16,1);

    ROLLBACK
END

GO

ALTER TABLE [dbo].[Payments] ENABLE TRIGGER [Payments_PreventBatchThatAreNotP]
GO


Oracle 12c Rman Duplicate Archive Log Not Verified I am trying to duplicate a DB from live to dev but it is failing. Can anybody advise what is going wrong? Both databases are running Oracle 12c. All database files seem to be copied over but it fails when trying to apply the archive logs.

I am running a full database and archive log backup before executing the DUPLICATE command.

Command

RUN{
DUPLICATE TARGET DATABASE TO UAT
FROM ACTIVE DATABASE
USING BACKUPSET
SPFILE
set control_files 'E:\oracle\product\12.1.0\oradata\UAT\CONTROLFILE\cf1.ctl','E:\oracle\product\12.1.0\oradata\UAT\CONTROLFILE\cf2.ctl','E:\oracle\product\12.1.0\oradata\UAT\CONTROLFILE\cf3.ctl'
set db_recovery_file_dest 'L:\ORACLE\ORADATA\UAT'
set db_create_file_dest 'E:\oracle\product\12.1.0\oradata'
set log_archive_dest_1 'LOCATION=L:\BACKUP\UAT\RMAN_BACKUPS'
set log_archive_dest_2 'LOCATION=L:\ORACLE\ORADATA\UAT\ARCHIVELOG'
set audit_file_dest 'E:\oracle\admin\UAT\adump'
set core_dump_dest 'E:\oracle\admin\UAT\cdump'
nofilenamecheck;
}

Error

Finished restore at 24-MAY-16

sql statement: alter system archive log current
current log archived

contents of Memory Script:
{
restore clone force from service  'ENGSPH'
           archivelog from scn  2661373093;
   switch clone datafile all;
}
executing Memory Script

Starting restore at 24-MAY-16
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service ENGSPH
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=5659
Oracle instance started

Total System Global Area    2147483648 bytes

Fixed Size                     3047720 bytes
Variable Size                822087384 bytes
Database Buffers            1308622848 bytes
Redo Buffers                  13725696 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''UAT'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
   shutdown clone immediate;
}
executing Memory Script

sql statement: alter system set  db_name =  ''UAT'' comment= ''Reset to original
 value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 05/24/2016 16:26:55
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script

ORA-19845: error in backupArchivedLog while communicating with remote database s
erver
ORA-17628: Oracle error 19571 returned by remote Oracle server
ORA-19571:  RECID  STAMP  not found in control file
ORA-19660: some files in the backup set could not be verified
ORA-19662: archived log thread 1 sequence 5659 could not be verified
ORA-19845: error in backupArchivedLog while communicating with remote database s
erver
ORA-17628: Oracle error 19571 returned by remote Oracle server
ORA-19571:  RECID  STAMP  not found in control file

Thanks for any help.

Could you please tell us how you connected to rman?

For example:

rman target sys/asdf@prod auxiliary sys/asdf@dev

The reason for you problem is that the two databases have trouble communicating with each other.

If you use auxiliary / because your rman session is located on the dev server this is not going to work. The reason is pretty simple as follows.

Lets say your rman session was opened on server dev. You use this connection:

rman target sys/asdf@prod auxiliary sys/asdf@dev

The auxiliary database uses the TNSalias "prod" to connect to the target database. The prod database uses the TNSalias "dev" to connect to the auxiliary database. This means you have to configure the tnsnames.ora on both sides to be able to connect to the other database. Since the rman session is on dev in this example you have to configure the TNSalias "dev" on dev as well.

Back to the example with auxiliary /, it is obvious now why it cannot work.

Then there are additional stuff to keep in mind. The auxiliary database needs to be restarted several times. In some cases you loose the connection to the auxiliary database when it shuts down. This means you have to configure the dev database in the SID_LIST of the listener on the dev server. And take care that the password file is present.

Well, this sounds like a lot configuration overhead. If you have to refresh your dev database frequently this is not a big deal. But I personally prefer to clone from backup location because this is much more simple. You can also skip the SPFILE clause when you create it manually. In my experience you need 2 or 3 attempts to work out the correct parameter conversion. If some parameters change on your prod database you might have to adjust this clause again.


When to use With Recompile I know what With Recompile means, and I know that it is not suggested at most of the times due to performance issues, no dmv's, etc..

Sometimes there are cases that it may help, but OPTION(RECOMPILE) may help too.

So that, when it is preferable to use With Recompile over OPTION(RECOMPILE)?

Thanks.

I don't think it ever is (unless you are stuck in SQL Server 2000 and are looking for pity - don't worry, you'll have it). I would opt for statement-level OPTION (RECOMPILE) every time. I'll quote the relevant bit from Paul White's blog post, Parameter Sniffing, Embedding, and the RECOMPILE Options:

  When a parameter-sensitivity problem is encountered, a common piece of advice on forums and Q&A sites is to "use recompile" (assuming the other tuning options presented earlier are unsuitable). Unfortunately, that advice is often misinterpreted to mean adding WITH RECOMPILE option to the stored procedure.

  Using WITH RECOMPILE effectively returns us to SQL Server 2000 behaviour, where the entire stored procedure is recompiled on every execution. A better alternative, on SQL Server 2005 and later, is to use the OPTION (RECOMPILE) query hint on just the statement that suffers from the parameter-sniffing problem. This query hint results in a recompilation of the problematic statement only;  execution plans for other statements within the stored procedure are cached and reused as normal.

  Using WITH RECOMPILE also means the compiled plan for the stored procedure is not cached. As a result, no performance information is maintained in DMVs such as sys.dm_exec_query_stats. Using the query hint instead means that a compiled plan can be cached, and performance information is available in the DMVs (though it is limited to the most recent execution, for the affected statement only).

  For instances running at least SQL Server 2008 build 2746 (Service Pack 1 with Cumulative Update 5), using OPTION (RECOMPILE) has another significant advantage over WITH RECOMPILE: only OPTION (RECOMPILE) enables the Parameter Embedding Optimization.

You can read on in the post to learn about this optimization, or read the whole thing to learn more about parameter sniffing and the different ways you can change SQL Server's behavior.


Backup everything except one record I want to backup my database except t1 table's one record (where option='siteurl').

I'm trying mysqldump's --where command.

mysqldump -u root --skip-extended-insert --where "option='siteurl'" konsertvdb t1 > sql.sql

This will work if I backup only the t1 table. But I want to backup all tables, except t1's one record (where option='siteurl') .

> mysqldump -u root --skip-extended-insert --where "option='siteurl'" konsertvdb > sql.sql
mysqldump: Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */ * FROM `wp_commentmeta` WHERE option='siteurl'': Unknown column 'option' in 'where clause' (1054)

*(wp_commentmeta is one of my other tables.)

Can you tell me is it possible?

Its too simple if you have gone through the mysqldump manual

First run a mysql dump ignore the table you would like to skip

mysqldump -p -u root db_name --ignore-table=db_name.tbl_name > sql.sql

Then once more for your table

mysqldump -p -u root db_name tbl_name --where "id!=2" >> sql.sql

As a note, if you are giving something in --where, it applicable for all the tables your are using. It will be useful in case of multi-tenant db.


setting a default collation for a postgresql database I have created a database with Turkish collation.

createdb -l tr_TR test_tr -E LATIN5 -T template0

Then a table with a row.

\c test_tr
create table turkish(one text);
insert into turkish values('inci');

Everything is expected.

test_tr=# \l
                                  List of databases
    Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
------------+----------+----------+-------------+-------------+-----------------------
 test_tr    | postgres | LATIN5   | tr_TR       | tr_TR       |

The problem, I expect İNCİ but it gives me INCI. This is wrong for Turkish language.

test_tr=# select upper("one") from turkish ;
 upper
-------
 INCI
(1 row)

It turns out, the default collation is still English.

test_tr=# select upper("one" collate "tr_TR"),upper("one"), "one" from turkish ;
 upper | upper | one
-------+-------+------
 İNCİ  | INCI  | inci

I know that this could be fixed via giving tr_TR collation while table creation like following:

create table turkish (one text collate "tr_TR");

So the question:

Is there any way to set default collation that Postgresql uses for table creation? That way, I can omit unnecessary collation specification on every table creation script.

you have to define it at database creation time, try this way:

postgres=# create database lctest encoding UTF8 LC_COLLATE 'tr_TR.utf8' LC_CTYPE 'tr_TR.utf8' template template0;
CREATE DATABASE
postgres=# \l
                                                  List of databases
                Name                 |     Owner      | Encoding |   Collate   |      Ctype    |   Access privileges
 -----------------------------------+----------------+----------+-------------+-------------+-----------------------
 en_US.UTF-8 | lctest                | postgres       | UTF8     | tr_TR.utf8  | tr_TR.utf8  |

postgres=# \c lctest
You are now connected to database "lctest" as user "postgres".
lctest=# create table lc_test (one varchar(10));
CREATE TABLE
lctest=# insert into lc_test values ('inci');
INSERT 0 1
lctest=# select upper(one)  from lc_test ;
 upper
-------
 İNCİ
(1 row)

Hope this help
MarcoP.


New database not using the model configuration On a SQL Server 2008 R2 Express instance, I have the "auto Close" option set to "False" on the [model] database.

However, When I execute CREATE DATABASE [test];, SQL Server creates the database with the auto close option set to true.

I checked sys.configurations just to make sure there was nothing pending (not the case).

I also tried to change the config on [model] to true, save, then change it back to false but I still have the same behavior.

If I create the database using the SSMS GUI (right click -> new database), it creates the database with the auto close option set to false

How can I make CREATE DATABASE create a database with auto close set to false?

I finally found the answer in Understanding SQL Express behavior: Idle time resource usage, AUTO_CLOSE and User Instances by Mike Wachal of the Microsoft SQL Express team:

  Supporting ClickOnce is something we wanted to do with SQL Express, so we modified the behavior of the CREATE DATABASE functionality in SQL Express in order to set AUTO_CLOSE to True for all databases.

So I guess I'll have to put a trigger in place to change auto close back to False when a new database is created.


SQL storage sizing - How to get statistics of what data is being accessed How can I monitor which data is being accessed and which frequency?

I'm in need to migrate several (very) small SQL Server instances, each which several small databases. Current configuration is based in a lot of also small servers with local storage. New configuration is based in a single server with a single NAS.

So far, the SQL Server memory and CPU sizing is OK. Also DB sizes and total IOPS. But there's no existing documentation of what data set is actually being accessed. So, basically, I don't have a clue about what are the real storage requirements since the total amount of IOPS may be for only a couple of tables (so it would work like a charm with just a couple of SSD) or if the whole set of databases are being scanned all the time and I'll need several dozens of disks.

So, back to the question: How can I "profile" and get statistics of what data is being accessed? Either at SQL or Windows level?

If the crux of the question is, "How do I get correct data to size my storage subsystem?" and you want to it fairly repeatedly across many servers, use windows performance counters.

Here is the list I would minimally have:

Logical Disk(*)\Current Disk Queue Length
Logical Disk(*)\Disk Read Bytes/Sec
Logical Disk(*)\Disk Reads/Sec
Logical Disk(*)\Disk Write Bytes/Sec
Logical Disk(*)\Disk Writes/Sec
Logical Disk(*)\Disk Transfers/Sec
Logical Disk(*)\Avg. Disk Sec/Read
Logical Disk(*)\Avg. Disk Sec/Write
Logical Disk(*)\Avg. Disk Sec/Transfer

The Avg. Disk Sec/XXX is going to give you the current milliseconds per operations (read/write). This is important to know so when you move to new storage you meet or exceed the current setup. It's also important as we don't want extremely high numbers here as that will manifest itself in other ways, making it "feel" like SQL Server is slow.

The Disk XXX/Sec is going to give you the number of operations (IOPS) with the Transfers being total IOPS (or Reads + Writes). This is also going to give you an IO profile of your server, knowing whether you're heavy read or write so that any caches could be tuned appropriately or more cache bought per storage unit.

The Disk XXX Bytes/Sec is going to give you an understanding of the size of the IO happening for reads/writes/total. This, in association with the Disk XXX/Sec should give you a better IO profile. Are you doing many small IOs, many large, a mixture, etc. It'll help you decide how to carve up LUNs, shares, etc. It'll also help you understanding what you'll need to do to any caches or cache sizes that may be in the mix.

  I know I'm using 30k IOPS. And the storage solution can provide that. BUT the proposed storage solution can achieve that using Fast VP / Fast Cache. That will actually mean that it's able to deliver 30k for some data (the hot one). But not for everything. I need to know what set of data requires those 30k IOPS. Because it's random data all the day long, I know the storage won't be able to cache or move that data to the fastest drives in order to obtain 30k. And I will end up with issues.

You have no control over what users end up wanting, and in some cases (insert) the data doesn't even exist yet. If you're having issues with storage at that level you'll want to go back and ask your storage admins to not be tiered. If you want to know every page that is touched per database, period, you'll want to create an extended events session and ask for a few TBs worth of storage to handle the crazy large amount of data you'll be doing. IMHO you're aiming at an unknown moving target that isn't feasible at all. Talk to your storage team.


Copy complete structure of a table in Oracle without using Backup When you create do a CTAS (create table as select) of a table you only get the structure, but lose the index, PK, FK, etc.

Example:

create table t1 select * from table2;

How can a copy of the table structure be made that includes these things without doing a backup?

select dbms_metadata.get_ddl('TABLE','SCHEMANAME','TABLENAME') DDL from dual;

TABLE is the type of object (you can also extract INDEX, VIEW, FUNCTION, PROCEDURE etc)

or:

create table t1 as
( select * from t2 where 3=4 );

dbms_metadata.get_ddl is the way to go, as it preserves various aspects that a CTAS query doesn't.


Data model to hold attendance records? I am designing an ERP like app for a local martial arts studio. Currently, the main functionality is to keep attendance records to training sessions. To do so, I have designed the following data model:

Some relations are:

A martial art practitioner can practice more than one martial art. But they must practice at least one martial art (I couldn't express the latter in my diagram).
An instructor for a given martial art must be chosen among the practitioners of that martial art.
In order to be counted as present or absent in a training session, a student should have had registered for that training session slot beforehand.

This is my first data model, so I am guessing that I have at least a couple of mistakes here. Could you please point out my mistakes here, if any?

There's a lot of redundancy between the mma and the bjj tables. If the only real data difference between an MMA practitioner and a BJJ practitioner is the belts and stripes, I'd consider pushing the unique fields from mma_practitioner and bjj_practitioner tables into the martial_arts_practitioner table; including adding a field indicating whether a given practitioner is mma or bjj (if mutually exclusive), or small fields (bit or 1 character) mma and bjj (if someone can be in both).

Even if other practitioner types will be added later, I'd still recommend this change; creating and maintaining an entire set of roster and session tables for each practitioner type wouldn't necessarily scale well. If there are more significant differences that aren't obvious, that could change this recommendation, but I can't think of anything off the top of my head.

An additional thought: it looks like instructors and students are both kept in the same table. This may make sense (especially if an instructor can also be a student in some other class or practition type). However, there are likely to be a number of fields unique to teachers (certifications, wages/salary, etc.) that would not apply to students. Similarly, there are likely to be a number of fields unique to students (grades, source [for marketing to other students], etc.) that wouldn't apply to instructors. And, presumably, every class/training session must have an instructor. It might make sense to have students and instructors in separate tables.


Backup / Export data from MySQL 5.5 attachments table keeps failing! Can anyone please help! - I have a large table in a MySQL 5.5 database. It is a table which holds a mixture of blobs/binary data and just data rows with links to file paths. It has just over a million rows.

I am having desperate problems in getting the data out of this table to migrate it to another server.

I have tried all sorts - mysqldump (with and without -quick), dumping the results of a query via the command line. Using a MySQL admin tool (Navicat) to open and export the data to file, CSV, or do a data transfer (line by line) to another DB and/or another server but all to no avail.

When trying to use the DB admin tool (Navicat), it gets to approx 250k records and then fails with an "Out of memory" error. I am not able to get any error messages from the other processes I have tried, but they seem to fall over at approximately the same number of records.

I have tried playing with the MySQL memory variables (buffer size, log file size, etc) and this does seem to have an effect on where the export stops (currently I have actually made it worse).

Also - max_allowed_packet is set to something ridiculously large as I am aware this can be a problem too.

I am really shooting in the dark, and I keep going round and round trying the same things and getting no further. Can anyone give me any specific guidance, or recommend perhaps any tools which I might be able to use to extract this data out??

Thanks in hope and advance!

A little more information below - following some questions and advice:

The size of the table I am trying to dump - it is difficult to say, but the sql dump gets to 27gb when the mysqldump dies. It could be approximately 4 times that in total.

I have tried running the following mysqldump command:

mysqldump --single-transaction --quick mydatabase attachments --password=abc123 -u root > d:\attachments.sql

And this gives the error:

  mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table attachments at row: 251249 – Mayb2Moro 4 hours ago

The server has 8gb RAM, Some of the relevant settings copied below. It is an INNODB database/table.

innodb_buffer_pool_size=3000M
innodb_log_file_size=1113M
max_allowed_packet=2024M
query_cache_size=52M
tmp_table_size=500M
myisam_sort_buffer_size=50M

Try increasing your net_write_timeout (probably a default value of 60secs is too small in your scenario of blob and binary values)

Ref:

net_write_timeout

and in general:

net_read_timeout


Access denied when connecting to mongosqld with MySQL I'm trying to connect to my MongoDB using MySQL through mongosqld. I have mongosqld running with a config file that looks like

security:
  enabled: true
mongodb:
  net:
    uri: test-db
    auth:
      username: usertest
      password: pass
      source: admin
schema:
    path: schema.drdl

I have it hosted on a mongo ODBC manager with SERVER=127.0.0.1, PORT=3071, DATABASE=test_db, UID=usertest?source=admin, PWD=pass. I am able to connect to and query this MongoDB through Excel using Mongo's tutorial for that, but I am not able to do the same with MySQL using Mongo's tutorial. When I try to connect from terminal with mysql 'user=usertest?source=admin' --default-auth=mongosql_auth -p I get ERROR 1045 (28000): Access denied for user 'usertest' andhandshake error: unable to saslStart conversation 0: Authentication failed.` from the mongosqld side. I am doing this on macOS. What could be causing this problem only for trying to connect from MySQL?

As per MongoDB documentation Connect from the MySQL Client You can use the command-line MySQL client to connect to the MongoDB Connector for BI.

Connect from MySQL without Authentication or TLS/SSL

To connect to a mongosqld instance listening on the MySQL default port 3307, run the following command:

mysql --protocol tcp --port 3307

Connect from MySQL with Authentication

Install the C Authentication Plugin as described in Install the C Authentication Plugin.

The following example uses the C authentication plugin to connect to a mongosqld instance listening on port 3307 as user reportsUser. The MySQL shell prompts for the password after the command has been entered.

mysql --user='reportsUser?source=admin' --default-auth=mongosql_auth -p

Note: This example assumes that the authentication plugin file mongosql_auth.so is located in the default MySQL plugin folder. The location of the plugin folder varies by platform, but you can locate it by running the following command:

mysql_config --plugindir

mysql_config.pl can find the plugin directory only on macOS and Linux hosts.

Important : The C Authentication plugin was developed against MySQL 5.7.18 Community Edition (64-bit), and tested with MySQL 5.7.18 Community Edition and the latest version of MongoDB Connector for BI. The plugin is not compatible with MySQL Server or Connector/ODBC driver version 8 and later.

Connect from MySQL with Authentication and TLS/SSL

To connect to a mongosqld instance listening on port 3307, as user grace using authentication mechanism PLAIN, and using specific TLS/SSL CA and x.509 certificates, run the following command:

mysql --enable-cleartext-plugin --protocol tcp --port 3307 \
  --user='grace?mechanism=PLAIN&source=$external' \
  --ssl-ca=/path_to_the_CAcert/ca.crt \
  --ssl-key=/path_to_my_certificate_key/mysql.key \
  --ssl-cert=/path_to_my_client_certificate/mysql.crt \
  -p

Important: The binary distribution of MySQL Community uses the yaSSL SSL library to encrypt connections. MySQL Enterprise uses OpenSSL which is compatible with MongoDB Connector for BI. Use MySQL Enterprise to connect to BI Connector over TLS / SSL.

If using the $external authentication source, wrap your username in single quotes or escape the $ character with a backslash to prevent your shell from performing interpolation.

For further your reference here


Setting up a CDBMS server I read something in wikipedia about CDBMS. I have no idea how to setup a CDBMS server!

Is it comparable to how I am currently using mysql?

I have some extremely large databases and I want to convert my dataset into CDBMS and test my codes with the new database. What are some things I need to be aware of before I start doing this, and how will the new code be different, if at all, from my existing code?

I don't have any more details. My supervisor asked me to check if CDBMS is a better way for analyzing a large dataset. So, I just wanted to setup a CDBMS and convert my dataset to it.

A CDBMS is a specialized database for handling queries like "show everything that is related to x". Unless that is what you want (and unless you don't want all the features of a traditional RDBMS like MySQL), you don't want a CDBMS.


SQL Server table and view show different values I have a view that selects data from a table that is showing different data.

The table is dbo.user, the view is dbo.users (don't judge, I inherited this system).  The view is simply select * from table.

If I run the following statements:

SELECT Field1, Field2 FROM dbo.[User] WITH (NOLOCK) ORDER BY Field1;

SELECT Field1, Field2 FROM dbo.Users WITH (NOLOCK) ORDER BY Field1;

I get different values for one of the fields.  See the attached pic:

Any ideas how this happens?  The table is clustered, has a pk and unique index.

When a view is created it is bound to the metadata it needs in order to execute.

Note:  https://msdn.microsoft.com/en-us/library/ms187821.aspx

That says that sp_refreshview: "Updates the metadata for the specified non-schema-bound view. Persistent metadata for a view can become outdated because of changes to the underlying objects upon which the view depends."

The outdated metadata binding causes the surprising "wrong" results.  Using schema-bound views is one way of avoiding the problem.

Read the link that Aaron Bertrand posted:

https://sqlblog.org/2009/10/10/bad-habits-to-kick-using-select-omitting-the-column-list


How to pinpoint root cause of excessive log file growth The log file stays pretty small (~500MB) until about 11PM, and at that point it begins growing until no disk space is available. The log file reached it's maximum size at 12:39AM, so the growth took place over approximately an hour and a half time frame. The drive that houses the logs only has about 40GB of total space available, so the log file can only grow to that 40GB. That should be plenty though as the data is fairly static and only about 50GB total. Here are a few more additional notes:

DB recovery model is full
Transaction log backups are taken every 15 minutes
A defrag job is called at 11PM but looking at the history it only runs for a few minutes max

How can I pinpoint the exact cause of this? It has to be a large transaction that is open but how can I determine that?

Best thing to do is to collect what caused the log file to grow by setting up an alert.

You can use sp_whoisactive  to run and log into the table (as DennisT suggested) or dmv's like sys.dm_tran_active_transactions and sys.dm_tran_database_transactions

--  Find Log space usage
-- http://stackoverflow.com/a/5085179/1387418
select
    SessionTrans.session_id as [SPID],
    enlist_count as [Active Requests],
    ActiveTrans.transaction_id as [ID],
    ActiveTrans.name as [Name],
    ActiveTrans.transaction_begin_time as [Start Time],
    case transaction_type
        when 1 then 'Read/Write'
        when 2 then 'Read-Only'
        when 3 then 'System'
        when 4 then 'Distributed'
        else 'Unknown - ' + convert(varchar(20), transaction_type)
    end as [Transaction Type],
    case transaction_state
        when 0 then 'Uninitialized'
        when 1 then 'Not Yet Started'
        when 2 then 'Active'
        when 3 then 'Ended (Read-Only)'
        when 4 then 'Committing'
        when 5 then 'Prepared'
        when 6 then 'Committed'
        when 7 then 'Rolling Back'
        when 8 then 'Rolled Back'
        else 'Unknown - ' + convert(varchar(20), transaction_state)
    end as 'State',
    case dtc_state
        when 0 then NULL
        when 1 then 'Active'
        when 2 then 'Prepared'
        when 3 then 'Committed'
        when 4 then 'Aborted'
        when 5 then 'Recovered'
        else 'Unknown - ' + convert(varchar(20), dtc_state)
    end as 'Distributed State',
    DB.Name as 'Database',
    database_transaction_begin_time as [DB Begin Time],
    case database_transaction_type
        when 1 then 'Read/Write'
        when 2 then 'Read-Only'
        when 3 then 'System'
        else 'Unknown - ' + convert(varchar(20), database_transaction_type)
    end as 'DB Type',
    case database_transaction_state
        when 1 then 'Uninitialized'
        when 3 then 'No Log Records'
        when 4 then 'Log Records'
        when 5 then 'Prepared'
        when 10 then 'Committed'
        when 11 then 'Rolled Back'
        when 12 then 'Committing'
        else 'Unknown - ' + convert(varchar(20), database_transaction_state)
    end as 'DB State',
    database_transaction_log_record_count as [Log Records],
    database_transaction_log_bytes_used / 1024 as [Log KB Used],
    database_transaction_log_bytes_reserved / 1024 as [Log KB Reserved],
    database_transaction_log_bytes_used_system / 1024 as [Log KB Used (System)],
    database_transaction_log_bytes_reserved_system / 1024 as [Log KB Reserved (System)],
    database_transaction_replicate_record_count as [Replication Records],
    command as [Command Type],
    total_elapsed_time as [Elapsed Time],
    cpu_time as [CPU Time],
    wait_type as [Wait Type],
    wait_time as [Wait Time],
    wait_resource as [Wait Resource],
    reads as [Reads],
    logical_reads as [Logical Reads],
    writes as [Writes],
    SessionTrans.open_transaction_count as [Open Transactions],
    open_resultset_count as [Open Result Sets],
    row_count as [Rows Returned],
    nest_level as [Nest Level],
    granted_query_memory as [Query Memory],
    SUBSTRING(SQLText.text,ExecReqs.statement_start_offset/2,(CASE WHEN ExecReqs.statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), SQLText.text)) * 2 ELSE ExecReqs.statement_end_offset end - ExecReqs.statement_start_offset)/2) AS query_text
from
    sys.dm_tran_active_transactions ActiveTrans (nolock)
    inner join sys.dm_tran_database_transactions DBTrans (nolock)
        on DBTrans.transaction_id = ActiveTrans.transaction_id
    inner join sys.databases DB (nolock)
        on DB.database_id = DBTrans.database_id
    left join sys.dm_tran_session_transactions SessionTrans (nolock)
        on SessionTrans.transaction_id = ActiveTrans.transaction_id
    left join sys.dm_exec_requests ExecReqs (nolock)
        on ExecReqs.session_id = SessionTrans.session_id
        and ExecReqs.transaction_id = SessionTrans.transaction_id
    outer apply sys.dm_exec_sql_text(ExecReqs.sql_handle) AS SQLText
where SessionTrans.session_id is not null -- comment this out to see SQL Server internal processes

More reference :

Investigating Transactions Using Dynamic Management Objects
A transaction log grows unexpectedly or becomes full in SQL Server
Managing Transaction Logs


SQL Server large transaction log for simple recovery model? help! Hi all i'm building a data warehouse and noticed that my tran log is massive (in simple recovery).  MDF file is 2.6 GB.  LDF file is 7.8 GB!!! Why is this??? i don't want a stupid tran log that's why i've put in simple recovery!  (i know the system needs tran log).  so i tried to shrink the DB and get this error message.

DBCC SHRINKDATABASE (< DB >)

DBCC SHRINKDATABASE: File ID 1 of database ID 5 was skipped because the file does not have enough free space to reclaim.
DBCC SHRINKDATABASE: File ID 2 of database ID 5 was skipped because the file does not have enough free space to reclaim.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

have plenty of disk space!  ARGH should change my job title to professional sql wrestler...

For completeness, converting my comment to answer :

If the database is EMPTY, what is the initial size of the transaction log defined when the DB was created ?

If the initial size of the T-log is defined more than you need, then thats the problem.

You should always carefully define the transaction logsize.


Connect to Mongo Shell on OpenShift I have a gear running a instance of mongoDB. I wish to connect to the mongo shell. How can I do it?

I have rhc and mongoDB installed on my computer.

I have RockMongo on the gear, but I cannot create a geospatial index from RockMongo.

I used rhc port-forward whic didn't give any error.

and then tried to connect using the following.

mongo -u<your mongodb user name> -p<the password for mongoDB>  --host <the ip number for mongoDB> --port <the port number> <app name>

for host I used the localhost address because I have port forward running. Now I can connect but every command i use results in "errmsg" : "unauthorised"

Work Arround

I used PuTTY to connect to the gear and form there I could connect to the mongo shell, and thus  solved the issue.

Some Articles that might be helpfull to others.

Remote Access to Your Application.
connecting-to-mongodb-gear-using-your-local-mongo-client-and-rhc-port-forwarding.


Reconciling @@TRANCOUNT Running SS2005.  I have a script that calls procedureA which in turn calls procedureB.

procedureA has no explicit transactions.  procedureB is split up into several small blocks of non-nested BEGIN and COMMIT TRANSACTION.

I have peppered procedureB with PRINT statements to show me the value of @@TRANCOUNT.  It's always at 0 or 1.

But when I check master..sysprocesses it shows open_tran at 1 or 2.  Where this extra transaction is coming from?

I am posting the 2 procedures below.  pr_Get12Relatives has been stripped of most of its content for clarity as it contains 1600 lines!  It just repeats the same behavior as it searches for different types of relationships.

procedureA = pr_GetRelatives

procedureB = pr_Get12Relatives

This thread answers my question..

When a DML statement is currently being executed in a session the open_tran and @@trancount is always at least 2.

For example

Connection 1

CREATE TABLE T( X INT PRIMARY KEY)

GO

BEGIN TRAN

INSERT INTO T VALUES(1)

WAITFOR DELAY '00:01'

ROLLBACK

Connection 2

INSERT INTO T VALUES(1) /*Blocks on Connection 1*/

Connection 3

(look at blocked connection 2)

SELECT lastwaittype,
       open_tran
FROM   master..sysprocesses
WHERE  spid = <spid of connection 2>

Results

+----------------------------------+-----------+
|           lastwaittype           | open_tran |
+----------------------------------+-----------+
| LCK_M_X                          |         2 |
+----------------------------------+-----------+


ORA-01691 (unable to extend lob segment) even though my tablespace has AUTOEXTEND on I am getting an error:

ORA-01691: unable to extend lob segment ABC.SYS_LOB0000167347C00131$$ by 1024 in tablespace XYZ

But this is very strange to me because the tablespace has AUTOEXTEND on. What is the likely reason that this happens and how can I overcome it?

Thanks.

Autoextensible datafiles still have a size limit.

select file_id, bytes, maxbytes from dba_data_files where tablespace_name = 'XYZ';

Maxbytes is the maximum size of the datafile, it can not grow beyond that. If bytes = maxbytes, you have two options:

1) increase the maximum size (maxbytes) as (15 is the file_id from above query), for example to 10G:

alter database datafile 15 autoextend on maxsize 20G;

Datafiles have a hard limit for the maximum size, it is 2^22 * block_size for smallfile tablespaces, and 2^32 * block_size for bigfile tablespaces.

If you use a tablespace with 8K block size, then the maximum size of your datafile is 32 GB. If your datafile is already at that size, you can not increase it any further, there comes option number two:

2) add a new datafile to the tablespace, for example:

alter tablespace xyz add datafile '/path/to/datafiles/xyz02.dbf' size 100M autoextend on next 100M maxsize unlimited;


Postgres fast update on non-indexed column Step 1

We have a delete query of the form that we are trying to speed up:

DELETE * FROM table_name
WHERE col_name in ('a','b',....'zzzz');

The operation deletes between 0.5-50% of the mass of the table. col_name is an indexed (non-unique) column.

This ran extremely slowly because each delete affected the index.

Step 2

We used a non-indexed tombstone boolean column called deleted with a DEFAULT FALSE. Our query now became:

UPDATE table_name
SET deleted = TRUE
WHERE col_name in ('a','b',....'zzzz');

This definitely runs quicker (60-200%), but seems to ignore the col_name index for large IN clauses. However, since the update only applies to an unindexed column, it is fast.

Step 3

We replaced the conditional to be:

UPDATE table_name
SET deleted = TRUE
WHERE col_name = 'a'
    OR col_name = 'b'
    OR ...
    OR col_name = 'zzzz';

Even though this utilizes the index, it runs at about the same speed as the DELETE from Step 1.

Is there a fast way to delete (or mark as deleted) a number of rows based on membership within a very large IN clause?

The database needs no concurrency handling as it is accessed by a dedicated single-threaded application.

Note: Individually performing the deletes/updates was an order of magnitude slower. The IN clause generally has between 20000 and 5 million elements.

I'm afraid there isn't any significant improvement to your scenario.

A few things to consider:

If your data statistics are good enough (i.e.: autovacuum has had the oportunity to properly ANALYZE your data), it is likely that PostgreSQL will use an Index Scan when you DELETE on the low side of the 0.5 .. 50% range, and a Sequential Scan on the high side. It's a matter of the estimated cost and the threshold where it will change from one method to the other will depend on some cost parameters you could actually tune.
It isn't likely that there are any faster ways... in any case the database need to locate which rows to delete (using an index or not), and delete them. For PostgreSQL, deleting a row is akin to changing the value of some hidden column (xmax), and later on, reclaiming the unused space through (auto)VACUUM. Other databases (specially, if they're non-transactional) perform this operation in significant different ways. Normally you trade higher speed by much lower safety/security.
The way PostgreSQL Multi Version Concurrency Control MVCC works, an UPDATE is basically equivalent to a DELETE followed by an INSERT. If you don't touch any indexed column, this UPDATE can take place as a Heap-Only Tuple (HOT) update. This won't have an effect when you delete a huge percentage of your table, because it isn't very likely that there is enough free room in your pages. In practice: don't expect an UPDATE to be faster than a DELETE. Your soft-deletes aren't likely to be faster than hard-deletes (as you already have been able to test).
If your data is not critical to you (i.e.: you use the database to process information that you actually have also stored somewhere else, and whose loss would then not be critical, because you could retrieve it again from sources), you can Use Unlogged Tables. You could also set some aggresive value to some of the database parameters: synchronous_commit (=off) and fsync (=off). I would discourage you from doing so unless your needs for speed are critical and the data can actually be easily recreated.


How to know how full the MySQL redo log is? Setting innodb_log_file_size to a higher value improves write performance, but increases crash recovery time (or even restart time, if I understand correctly).
Is there a way to know, at any time, how full the redo log is? To get an idea of how long the crash recovery would take.
Subsidiary question: is there a way to force flushing the redo log, while MySQL is running?

The answer depends on which version of MySQL you are using.  Meanwhile, here are two 'old' answers:

innodb_log_file_size should be at least 10 times the size of the largest BLOB or TEXT being used.  (That has been relaxed recently some.)

Adjust innodb_log_file_size so the following is roughly 60.  This computes how often (in minutes) the log is rotated.  Once every 60 minutes is recommended, but if this gives you anywhere between 20 and 180, your setting is probably fine.
  Uptime / 60 * innodb_log_file_size / Innodb_os_log_written

The recovery time has improved recently.  So, again, upgrading may be useful.

Meanwhile, adjust according to the above guidelines.


Oracle random number as default column value I'm trying to create a table in OracleDB and assign random number to one of the columns as a default. I have this setup working in postgresql

create table table_name (
    column integer NOT NULL DEFAULT (random())
)

how can I do something similar in oracle (preferably without triggers)?

I've tried this

create table table_name (
    column integer generated always as (dbms_random.random) virtual
)

but oracle doesn't like it since the function is non deterministic.

Trigger is the only way to do it.

Virtual column is different from what you want, and that will not accept DBMS_RANDOM.RANDOM, because that is not deterministic:

Column Expressions

  ...it can contain only the following forms of expression:

  Columns of the subject table — the table being created, altered, or indexed
  Constants (strings or numbers)
  Deterministic functions — either SQL built-in functions or user-defined functions

The syntax of default values for columns is similar to your original statement, but that does not accept DBMS_RANDOM.RANDOM because PL/SQL is not allowed there:

Restrictions on Default Column Values

  Default column values are subject to the following restrictions:

  A DEFAULT expression cannot contain references to PL/SQL functions or to other columns, the pseudocolumns LEVEL, PRIOR, and ROWNUM, or
  date constants that are not fully specified.
  The expression can be of any form except a scalar subquery expression.


Filtered foreign keys I have two tables:

Contact (ID, Type);
Address (ID).

Is it possible to define a foreign key from Address (ID,'A') to Contact (ID,Type)?

Assuming that you want to implement a supertype/subtype relationship between the two tables (an Address is a Contact), you can, with a column having a fixed value of 'A' and use it for the foreign key:

CREATE TABLE Contact
  ( ID INT NOT NULL IDENTITY,
    Type VARCHAR(10) NOT NULL,
    PRIMARY KEY (ID, Type)
  ) ;

and

CREATE TABLE Address
  ( ID INT NOT NULL,
    Type  VARCHAR(10) NOT NULL DEFAULT 'A',
    CHECK (Type = 'A'),
    PRIMARY KEY (ID, Type),
    FOREIGN KEY (ID, Type)
      REFERENCES Contact (ID, Type)
  ) ;

Alternatively we could declare Type in Address as a persisted computed column (the check constraint would not be needed then):
Type AS CAST('A' AS varchar(10)) PERSISTED

CREATE TABLE Address
  ( ID INT NOT NULL,
    Type AS CAST('A' AS varchar(10)) PERSISTED NOT NULL,
    PRIMARY KEY (ID, Type),
    FOREIGN KEY (ID, Type)
      REFERENCES Contact (ID, Type)
  ) ;


Scan Count of a query increased from 1 to 2,226 after creating a non-clustered index On my query 1st time it was taking almost 39 seconds and showing me non-clustered index is missing and

  Scan count 1, logical reads 14553, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
  read-ahead reads 0

After Creating the non-clustered index the duration of the query execution comes down to 1sec and it's showing

  Scan count 2266, logical reads 4539, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
  read-ahead reads 0

Logical reads is decreased after creating the non-clustered index, but Scan count increased to 2266. So my question is the performance of the query is developed or the Scan count 2266 make my query non-optimal?

First scenario, it's scanning the whole table and looking through 14553 pages of data. Second scenario, it's doing 2266 seeks (but counted as range scans) which each look at just 2 pages. So the second one is way better. Plus, many of those seeks will probably be looking at pages which have just been looked at, so on a cold cache it will be an even larger performance benefit. And, the second is more likely to parallelise better, being lots of small operations rather than a large one (which could still be parallelised, but it's more effort).


Where are PostgreSQL databases stored on my computer? So this might be a stupid question, but I'm really new to this.
So I'm making a Postgres database on the postgres server, that I started up with this command:

sudo -u postgres psql

And then I did the command:

CREATE DATABASE database;

Which supposedly created the database.
But where is this database? Where can I find it on my computer?

Where exactly your data is stored depends on how you installed and configured PostgreSQL. Running the

show data_directory

command as the postgres user (from inside the psql tool or another client) will tell you where it is though. For example, if you installed it from the debian or ubuntu repositories you would probably find your data files are somewhere like /var/lib/postgresql/9.2/main.

There probably isn't anything terribly interesting in there though. Sometimes there are a few configuration files like pg_hba.conf (the windows version puts them there at least) but if you're on debian you'll find those under /etc/postgresql/.

If you're expecting your database to be a single file that you can move around between systems, etc, you're probably better off using a simpler embedded database like sqlite.


help me for writing an sql query I want to write a query to select all the rows where the row value changes from previous row. Please see the image attached above.

CREATE TABLE traffview.status_data (
    id serial NOT NULL,
    site_id integer,
    rtc timestamp without time zone,
    sigplan smallint,
    PRIMARY KEY (id)
    );

INSERT INTO traffview.status_data(
    id,
    site_id,
    rtc,
    sigplan
    )
    VALUES (?, ?, ?, ?);

This should work:

WITH cte(id, controller_id, RTC, signal_plan, rw) AS
(
     SELECT
         id,
         controller_id,
         RTC,
         signal_plan,
         ROW_NUMBER() OVER (PARTITION BY signal_plan ORDER BY id) rw
     FROM your_table)

     SELECT
         id,
         controller_id,
         RTC,
         signal_plan
     FROM cte
     WHERE rw = 1;


Amazon RDS MySQL Troubleshooting High Sessions I just migrated my forums application to AWS. I'm in the middle of tweaking things for performance and I noticed that the number of sessions for my RDS instance (t3.large) is consistently exceeding 2.0. At first there was a bad query that was writing way too much, but now that I've eliminated that, I can't figure out what's going on here. Here's some screenshots of the performance insights:

It looks like there's something going on with the binlog and innodb_log_file that is causing some of the sessions to block, but I'm unclear how to fix this. I've tried increasing the log file size from the default, but I'm not sure what else to do.
One thing to note is that my CPU usage on this RDS instance is very, very low, despite constant usage.

In addition to what was already stated, I took a look at the amount of freeable RAM on the instance. My RAM useage was also very good, with plenty of usable RAM.

The waits shown in the images are log file related -- the only thing that really should affect those is your disk IO. So begun the hunt: my CloudWatch metrics were showing a high number of writes (about 230 per second) and a very low number of reads (1 to 2 per second). That was somewhat of a surprise, but it turned out there was some index rebuilding that was being done on a table with over 5 million rows in it.

From this point, I started wondering what my limit on IOPS was for my RDS instance. As it turned out, I was using general storage, not provisioned IOPS, and in AWS General Storage IOPS baseline is about 3 IOPS per GB. Since my database was large but not enormous, I had only selected 50GB of General SSD storage. That means that my disk operations were limited to only about 150 IOPS for baseline. There are burst IOPS, up to 3000 IOPS for about 30-35 minutes, but I had already depleted this and wasn't aware that I needed to monitor these credits in CloudWatch to know when I might start getting throttled.

In the end, I increased the size of my RDS Storage and the problem immediately subsided.


Postgresql Update Table row values by Grouped Column First of all a little bit of background:

I currently have a table which contains call data both inbound and outbound from my business, up until recently there was no api_id data available, however this has come in with the last update to my suppliers API, as such i have some historical call data that can be grouped by call_id.

What i am looking to do is update the existing rows in the database that do not have an api_id with a random string of characters grouping them by their call_id.

| ... | call_id | api_id |
|------------------------|
| ... | 123     | AAA    |
| ... | 456     |        |
| ... | 456     |        |
| ... | 788     |        |
| ... | 789     |        |
| ... | 789     |        |

Would then become:

| ... | call_id | api_id |
|------------------------|
| ... | 123     | AAA    |
| ... | 456     | GHA    |
| ... | 456     | GHA    |
| ... | 788     | ZPM    |
| ... | 789     | LFF    |
| ... | 789     | LFF    |

Effectively an update by grouped query, which i have done a search for but was unable to find anything.

If anyone here could help or point me in the right direction it would be much appreciated.

Database Technology: Postgresql 9.5.4

Simple answer - as the dummy value in api_id doesn't matter: use the call_id:

update the_table
    set api_id = call_id::text
where api_id is null;

Complicated answer:

Assuming you have some way of generating a random string, you could do something like this (untested):

update the_table
   set api_id = t.random_string
from (
  select distinct on (call_id) call_id, random_string() as random_string
  from the_table
  where api_id is null
  order by call_id
) t
where t.call_id = the_table.call_id
 and the_table.api_id is null;

This generates one row with per call_id in the inner query and generates a random value for each of that. This result is then used to update the target table.

The above assumes that if there is already value for a specific call_id there is one for all rows. It won't "fill" in missing api_id for other rows (e.g. if there was a row with call_id = 123 but no api_id)


MySQL stress test tool, remote server preferred For MySQL stress test there exists programs:
http://dev.mysql.com/doc/mysqltest/2.0/en/test-programs.html

Also mysqlslap exists: http://dev.mysql.com/doc/refman/5.5/en/mysqlslap.html

Is it possible to test remote server ?
With proper configuration to connect both servers, is it possible to make stress test from a remote server ?

The vast majority of database benchmarking tools allow the user to specify a host and a login - Hammerdb does, as does SysBench. The last programme on your first link also allows a host to be specified.

Check out this interesting link for real test datasets.


How to join these queries? So I currently have this query:

SELECT v.Voorstellingsnummer, COUNT(r.Voorstellingsnummer)
FROM reserveringen r, voorstelling v
WHERE r.Voorstellingsnummer = v.Voorstellingsnummer
GROUP BY r.Voorstellingsnummer HAVING COUNT(*) < 50

That query outputs this table:

Voorstellingsnummer  COUNT(r.Voorstellingsnummber)
-------------------  -----------------------------
4                    1
5                    2

The other query is this one:

SELECT v.Tijd, v.Voorstellingsnummer
FROM voorstelling v
WHERE v.Vestigingsnaam = 'Amsterdam'
AND v.Filmnaam = 'Avengers'

...which outputs this:

Tijd                 Voorstellingsnummer
-------------------  -------------------
2019-03-24 11:12:00  5
2019-03-24 15:12:00  4

Considerations

I need to add a COUNT(v.Voorstellingsnummer) column to the last image the same way as it is in the first one.
And then I need to add those COUNTS together to give a totalcount.
That total count should be lower than a set number and if it's higher it should not be shown.

When I do this:

SELECT v.Tijd, v.Voorstellingsnummer, COUNT(v.Voorstellingsnummer)
FROM voorstelling v
WHERE v.Vestigingsnaam = 'Amsterdam'
AND v.Filmnaam = 'Avengers'

...it returns this:

Tijd,                Voorstellingsnummer, COUNT(v.Voorstellingsnummer)
2019-03-24 11:12:00, 5,                   2

...but instead it should show both the times form the second picture both with a count of 1.

Those counts of 1 should be combined with the other table so the final counts of
Voorstellingsnummer=4 should be 2
and Voorstellingsnummer=5 should be 3

Can anyone tell me how to do this?

Further information

Basically if the table reserveringen look has (Voorstellingsnummer = 4) 6x
and the table voorstelling has (Voorstellingsnummer = 4) 2x
the count outputted by the query should be 8x.

However, currently if (Voorstellingsnummer = 4) appears 3x in the table voorstelling, but has 0 appearances in the table reserveringen it won't be counted.

So what I'm thinking is running this query:

SELECT v.Voorstellingsnummer, COUNT(r.Voorstellingsnummer)
FROM reserveringen r, voorstelling v
WHERE r.Voorstellingsnummer = v.Voorstellingsnummer
GROUP BY r.Voorstellingsnummer HAVING COUNT(*) < 50

for the table reserveringen and the table voorstelling. That should result in two tables. The result of the query for table voorstelling should look like this (using number previously mentioned):

Tijd         Voorstellingsnummer   COUN
----------   -------------------   ----
something    5                      1
something    4                      1

..and then the result for the query for table reserveringen should look as follows:

Voorstellingsnummer   COUNT
-------------------   -----
5                      2

That's because table reserveringen has no Tijd column.

Those two tables should then be combined into the following one:

Tijd         Voorstellingsnummer    COUNT
---------    -------------------    -----
something    5                      3
something    4                      1

Basically both Voorstellingsnummer 4/5 approve of the condition Vestigingsnaam='Amsterdam' AND Filmnaam='Avengers'; however only Voorstellingsnummer 5 appears in the reserveringen table. Because of that I need to know how much appearances Voorstellingsnummer = (Some number) has over both tables.

Note: Reserveringen means reservations in dutch, and voorstelling is show.
Some shows might have reservations but some might not. Because the show without reservations should still be listed (as it's not full yet).

In advance i've never written anything in mysql, but the theory should still be there in case of small syntax errors.

In general i'd advice using INNER JOINS, instead of joining your tables in a WHERE clause, due to easier readability and better coding practice, as it makes it easy to change the sql inner join to an outer join as i've shown below:

SELECT v.Voorstellingsnummer, COUNT(r.Voorstellingsnummer)+1
FROM voorstelling v
INNER JOIN reserveringen r ON r.Voorstellingsnummer = v.Voorstellingsnummer
WHERE v.Vestigingsnaam = 'Amsterdam'
AND v.Filmnaam = 'Avengers'
GROUP BY r.Voorstellingsnummer HAVING COUNT(*) < 50

I am however very confused as to how you want the counts added? The above query should return what you're asking for. As it sounds like you just want the initial counts to be added with 1. To have this match more function like instead of a hardcoded value, i would have to understand why 4 and 5 should have a count of 1 in the other table?


SQL server databases stuck in restoring state I have a Sharepoint server. We had an issue with our backup tool and now some of my databases are stuck in restoring state!

Is it possible to stop the restoring process? and also, How can I make sure the database integrity has not been compromised?

This is likely caused by the restore script adding the WITH NORECOVERY parameter, to make the database ready for a transaction log apply after the restore.

The database is now waiting for the latest transaction log file.

You can either:

Apply the latest transaction log, using RESTORE LOG database_name FROM backup_device WITH RECOVERY; ... or
Restore the database again, but this time using ... WITH RECOVERY; ... or
Force the database out of restoring mode by executing: RESTORE DATABASE YourDb WITH RECOVERY;

Before you do this, please make sure you understand the implications of these options. You may cause data loss if you are not careful.

See this for details:

http://technet.microsoft.com/en-us/library/ms177446.aspx
https://stackoverflow.com/questions/520967/sql-server-database-stuck-in-restoring-state


Best practice to query administrative views for instance level elements I want to query a monitor element at instance level (for example the memory used for the instance) via a query like this:

SELECT MEMBER, MAX_MEMBER_MEM, CURRENT_MEMBER_MEM, PEAK_MEMBER_MEM
FROM TABLE(SYSPROC.ADMIN_GET_MEM_USAGE()) AS T

However, in order to execute the query I need to be connected to a database. What method do you use to query this kind of elements (instance level) and establishing a connection (database level):

Connect to the first database available in the db directory.
Create another database just for monitoring purposes.

1) For this scenario, I have to give the necessary grants in at least one database of each instance.

db2 list db directory | awk '/alias/ {print $4}'
db2 connect to $i
db2 -vf query.sql

2) In this scenario, I have to create a database for monitoring purposes in each instance of each server, and I give the necessary grant in this database.

db2 connect to monitodb
db2 -vf query.sql

I have hundreds of databases, distributed in many instances on different servers. And for this reason, the way to do a query creates a great impact in the set of database I am currently administering.
|

This is the classic question / fight over whether monitoring should have a monitoring agent installed on each server, or if it should be "agentless".

With an agent installed on each server, a process/script/etc. wakes up every so often, collects data, and reports it back to a central location (i.e. monitoring server).

With an agentless solution, the central server polls each monitored server/database/etc on a schedule to collect the information.  Cacti and Nagios typically use this solution.

There are pros and cons to each method.  There is not necessarily a best practice for which method to use, discussion usually just results in a holy war (similar to Emacs vs. vi, DB2 vs Oracle, ...).