How to avoid a race condition with cross db proc calls? and other concerns I am not even sure this question is necessary but I am curious to know everyone's thoughts. I have two databases on the same server, dbFoo, dbBar. dbFoo has the following table please note this is a way dumbed down example, and the syntax may not be correct as I am rushing and way more interested in the answer to the underlying issue then the code to do it...

CREATE TABLE dbo.CodeNumbers(
CodeNumbersID INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
CodeValue VARCHAR(30) NOT NULL
IsUsed BIT NOT NULL DEFAULT(0)
);

dbo.CodeNumbers gets populated with a monthly CSV provided, the import method of your choice is already written to get them in there. We are NEVER given a duplicate code.

Lets assume for arguments sake we have 10,000,000 rows in the table. that all follow this format when imported:

1, 'ajdirjfisofklrlfo039402', 0 all the way till
10000000, 'fkeiir9489', 0

Now in dbBar I have 2 stored procedures the first should access the first un-used code in dbFoo, return it in an out variable and mark it as used. So I have something like:

CREATE PROCEDURE GetNextUseableCode
   @CodeOut VARCHAR(30) OUTPUT,
   @CID INT OUTPUT
AS
   SELECT @CID = CodeNumbersID, @CodeOut = CodeValue
   FROM dbFoo.dbo.CodeNumbers
   WHERE IsUsed = 0

   UPDATE dbFoo.dbo.CodeNumbers
   SET IsUsed = 1
   WHERE CodeNumbersID = @CID

The code that calls the procedure from dbBar is accessed by 200k sessions a day at various times. When dbFoo.Codes has no more to return, its fine all is well and good, the application is simply told sorry no more check back tomorrow.

I have 3 main questions..

Is there anything special I would need to have in the code to avoid race conditions, and if so what would be best to handle for this without bringing the system to its knees.
Is their an efficient way to ensure the next code grabbed whenever the procedure is called, is the next one in chronological order within the ID column.
Is there any other concerns that I am not looking at right now that could issue big problems, and what would be an eloquent way to handle this situation?

I understand this is a long pretty open ended question, I have some coded up solutions, but I feel like there are much better ways to get the results I want.

Thanks in advance as always for all help.

There is nothing in your SELECT that dictates order at all. It is also not protected from two sessions reading the same row. To see that it is not safe:

Create a throwaway table with a key column.

CREATE TABLE dbo.GeneratedIDs(ID INT PRIMARY KEY);

Run this code in a loop from two different sessions:

SET NOCOUNT ON;

DECLARE @DECLARE @CID INT, @CodeOUt VARCHAR(30);

SELECT @CID = CodeNumbersID, @CodeOut = CodeValue
  FROM dbFoo.dbo.CodeNumbers
  WHERE IsUsed = 0

UPDATE dbFoo.dbo.CodeNumbers
  SET IsUsed = 1
  WHERE CodeNumbersID = @CID

INSERT dbo.GeneratedIDs SELECT @CID;

GO 100000

Check the output for these - they'll happen:

  Msg 2627, Level 14, State 1
  Violation of PRIMARY KEY constraint 'PK_hexcode'. Cannot insert duplicate key in object 'dbo.GeneratedIDs'. The duplicate key value is (<some value>).

Or you might see deadlocks if you wrap the SELECT/UPDATE in an explicit transaction:

  Msg 1205, Level 13, State 52
  Transaction (Process ID 57) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

To get around this problem, and to ensure that the ID you get is the lowest one available, you could do this:

BEGIN TRANSACTION;

SELECT TOP (1) @CID = ...
FROM dbFoo.dbo.CodeNumbers WITH (XLOCK, HOLDLOCK)
WHERE IsUsed = 0
ORDER BY CodeNumbersID;

UPDATE ...

COMMIT TRANSACTION;

Note that I added an explicit transaction and also XLOCK/HOLDLOCK hints to prevent two simultaneous sessions from reading the same row. Of course, this has an impact on concurrency (which, unfortunately, is exactly what you want and need here).

Other ways to do this include just updating the row and then using a table variable to capture values from the OUTPUT clause:

DECLARE @x TABLE(CodeOut VARCHAR(30), CID INT);

;WITH x AS
(
  SELECT TOP (1) CodeNumbersID, CodeValue, IsUsed
    FROM dbFoo.dbo.CodeNumbers WITH (XLOCK, HOLDLOCK)
    WHERE IsUsed = 0
    ORDER BY CodeNumbersID
)
UPDATE x SET IsUsed = 1
  OUTPUT inserted.CodeValue, inserted.CodeNumbersID INTO @x;

SELECT @CodeOut = CodeOut, @CID = CID FROM @x;

Per Paul's update, yes, you could also do this without the table variable:

;WITH x AS
(
  SELECT TOP (1) CodeNumbersID, CodeValue, IsUsed
    FROM dbFoo.dbo.CodeNumbers WITH (XLOCK, HOLDLOCK)
    WHERE IsUsed = 0
    ORDER BY CodeNumbersID
)
UPDATE x
  SET @CodeOut = x.CodeValue, @CID = x.CodeNumbersID, IsUsed = 1;

(Though I am not all that fond of this syntax; not sure why. Might be the same reason I always forget it exists.)

You could change the caller to expect a resultset instead of two output parameters, but that is extra work too. In both cases you'd still need to ensure you got the lowest ID available, which likely means a CTE with a SELECT and the same hints. Some discussion here. I also talk about a similar approach in this blog post but I didn't get into anything about concurrency and two sessions trying to delete the same row at the same time. Obviously in that case only one of them can win, but with an UPDATE they could both manage to be successful (at least in theory).

To make things easier, you could relax the restriction that the "next" ID handed out is the lowest ID available. But you still need the isolation through the hints to ensure that two simultaneous sessions don't happen to read the same value (which could happen regardless of ordering). Hopefully with a suitable index these won't destroy concurrency.


SQL Server Concurrency Issue We have a SQL server concurrency issue we are facing and would like some ideas on how to solve it. There are several procedures we have written the contain INSERT/UPDATE statements on tables in a Set based manner. I will try to illustrate an example here

Lets assume that the isolation level set by the AppServer over the JDBC connection is READ COMMITTED

DELETE FROM TargetAmountTable
/*whole bunch of JOINS on other tables*/
WHERE SourceAmountSetTable.isModified = 1

INSERT INTO TargetAmountSetTable
SELECT /**whole bunch of JOINS**/
WHERE SourceAmountSetTable.isModified = 1

UPDATE TargetAmountSetTable
SET TargetAmountSetTable.isModified = 1
/*whole bunch of JOINS*/
WHERE SourceAmountSetTable.isModified = 1

INSERT INTO TargetAmountTable
SELECT /*whole bunch of JOINS*/
WHERE SourceAmountSetTable.isModified = 1

UPDATE SourceAmountSetTable
SET SourceAmountSetTable.isModified = 0
FROM /*whole bunch of JOINS*/
WHERE SourceAmountSetTable.isModified = 1

So the problem we are facing is, when there are multiple processes trying to do the INSERT on the [TargetAmountTable], duplicate rows are added since the last UPDATE statement is not yet executed

Any ideas on how to resolve this?

In general what you want here is an explicit transaction around your code.

BEGIN TRANSACTION
... your code ....
COMMIT

However there are down sides to concurrency here as well.  While your transaction is open any locks needed to perform the tasks will be held until the end of the transaction.  That means that the next set of code that needs to run will have to wait until this one is completed.

Even worse you increase your odds of creating a deadlock.  A deadlock would occur when session 1 is holding a set of locks X.  Session 2 is holding a set of locks Y then needs to get a hold of some of the resources held by X.  If session 1 then needs resources in Y you get into a situation where neither session can ever complete.  This causes a deadlock and one of the sessions will be rolled back.

The solution to this is to make sure that your transactions take as little time as possible.  What worries me is the whole bunch of JOINs comments.  That tends to mean the commands will take a fair amount of time increasing your odds of blocking and deadlocking.  If I'm wrong and the whole piece of code is fast then you shouldn't have major issues (there is always a chance of deadlocking, speed just reduces it).

Because your example is so simplified it's going to be hard to go beyond such a general piece of advice.  If you'd like to post a better idea of the actual code we might be able to help you in other ways.

Additional information on blocking & deadlocking here.


Database design: Two 1 to many relationships to the same table I have to model a situation where I have a table Chequing_Account (which contains budget, iban number and other details of the account) which has to be related to two different tables Person and Corporation which both can have 0, 1 or many chequing accounts.

In other words I have two 1-to-many relationships with the same table Chequing account

I would like to hear solutions for this problem which respect the normalization requirements.
Most solutions I have heard around are:

1) find a common entity of which both Person and Corporation belong and create a link table between this and the Chequing_Account table, this is not possible in my case and even if it were I want to solve the general problem and not this specific instance.

2) Create two link tables PersonToChequingAccount and CorporationToChequingAccount which relate the two entities with the Chequing Accounts. However I don't want two Persons to have the same chequing account, and I don't want to have a natural person and a Corporation to share a chequing account! see this image

3) Create two foreign keys in Chequing Account which point to Corporation and Natural Person, however I would thus enforce that a Person and a Company can have many chequing accounts however I would have to manually ensure that for each ChequingAccount row not both relations point to Corporation and Natural person because a checquing account is either of a corporation or of a Natural Person. see this image

Is there any other cleaner solution to this problem?

Relational databases are not built to handle this situation perfectly.  You have to decide what is most important to you and then make your trade-offs.  You have several goals:

Maintain third normal form
Maintain referential integrity
Maintain the constraint that each account belongs to either a corporation or a natural person.
Preserve the ability to retrieve data simply and directly

The problem is that some of these goals compete with one another.

Sub-Typing Solution
You could choose a sub-typing solution where you create a super-type that incorporates both corporations and persons.  This super-type would probably have a compound key of the natural key of the sub-type plus a partitioning attribute (e.g. customer_type).  This is fine as far as normalization goes and it allows you to enforce referential integrity as well as the constraint that corporations and persons are mutually exclusive.  The problem is that this makes data retrieval more difficult, because you always have to branch based on customer_type when you join account to the account holder.  This probably means using UNION and having a lot of repetitive SQL in your query.

Two Foreign Keys Solution
You could choose a solution where you keep two foreign keys in your account table, one to corporation and one to person.  This solution also allows you to maintain referential integrity, normalization and mutual exclusivity.  It also has the same data retrieval drawback as the sub-typing solution.  In fact, this solution is just like the sub-typing solution except that you get to the problem of branching your joining logic "sooner".

Nevertheless, a lot of data modellers would consider this solution inferior to the sub-typing solution because of the way that the mutual exclusivity constraint is enforced.  In the sub-typing solution you use keys to enforce the mutual exclusivity.  In the two foreign key solution you use a CHECK constraint.  I know some people who have an unjustified bias against check constraints.  These people would prefer the solution that keeps the constraints in the keys.

"Denormalized" Partitioning Attribute Solution
There is another option where you keep a single foreign key column on the chequing account table and use another column to tell you how to interpret the foreign key column (RoKa's OwnerTypeID column).  This essentially eliminates the super-type table in the sub-typing solution by denormalizing the partitioning attribute to the child table.  (Note that this is not strictly "denormalization" according to the formal definition, because the partitioning attribute is part of a primary key.)  This solution seems quite simple since it avoids having an extra table to do more or less the same thing and it cuts the number of foreign key columns down to one.  The problem with this solution is that it doesn't avoid the branching of retrieval logic and what's more, it doesn't allow you to maintain declarative referential integrity.  SQL databases don't have the ability to manage a single foreign key column being for one of multiple parent tables.

Shared Primary Key Domain Solution
One way that people sometimes deal with this issue is to use a single pool of IDs so that there is no confusion for any given ID whether it belongs to one sub-type or another.  This would probably work pretty naturally in a banking scenario, since you aren't going to issue the same bank account number to both a corporation and a natural person.  This has the advantage of avoiding the need for a partitioning attribute.  You could do this with or without a super-type table.  Using a super-type table allows you to use declarative constraints to enforce uniqueness.  Otherwise this would have to be enforced procedurally.  This solution is normalized but it won't allow you to maintain declarative referential integrity unless you keep the super-type table.  It still does nothing to avoid complex retrieval logic.

You can see therefore that it isn't really possible to have a clean design that follows all of the rules, while at the same time keeping your data retrieval simple.  You have to decide where your trade-offs are going to be.


Alert and notification option in job properties During SQL Agent Job configuration I see the options to define ALERT and NOTIFICATION.
I see that notification is what I'd expect, a notification of a job failure for example.
But I do not understand the requirement to create a notification within the job configuration. For example there I can configure to notify me about general Sql Server events or the Memory Manager\Max Workspace Memory Counter to fall under a limit.

  Are these general server health and performance monitoring options
  that are completely independant of the job I am actually configuring?

I am staring at the screen wondering whether I am just stupid or this is a design failure of Microsoft?

Magier -- The alerts are designed to allow you to capture and sent a notification for just about any condition that occurs on your SQL server. One common practice is to have the SQL agent looking for different fatal error severity levels that could be returned as a result of an error being raised from an executed command. Or for specific errors that are reported in the SQL logs that could indicate IO issues right up to database corruption.

Brent Ozar has a write up about some of the common alerts that he recommends as part of their sp_blitz procedure.

You could most certainly add many more if you don't have an additional monitoring service running to give you a heads up when something isn't within the range that you're expecting or warnings/errors are reported in the SQL logs.


Trigger BEFORE INSERT from date to datetime I have this schema:

CREATE TABLE `prices` (
  `id_product` int(11) unsigned NOT NULL,
  `date` date NOT NULL, # Important: DATE
  `value` decimal(8,5) unsigned NOT NULL DEFAULT '0.00000'
  PRIMARY KEY (`id_product`, `date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `prices_changes` (
  `id_product` int(11) unsigned NOT NULL,
  `date` datetime NOT NULL, # Important: DATETIME
  `value` decimal(8,5) unsigned NOT NULL DEFAULT '0.00000'
  KEY (`id_product`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DELIMITER ;;
CREATE TRIGGER `prices_before_insert`
  BEFORE INSERT ON `prices`
  FOR EACH ROW
    INSERT INTO `prices_changes` (`id_product`, `date`, `value`) VALUES (NEW.`id_product`, NEW.`date`, NEW.`value`);;
DELIMITER ;

INSERT INTO `prices` SET
  `id_product` = 1,
  `date` = "2016-08-11 13:50:00", # Is a DATETIME value
  `value` = "20.50"
ON DUPLICATE KEY UPDATE `value` = VALUES(`value`);

mysql> SELECT * FROM `prices` WHERE id_product = 1;
+------------+------------+---------+
| id_product | date       | value   |
+------------+------------+---------+
| 1          | 2016-08-11 | 20.50   |
+------------+------------+---------+

mysql> SELECT * FROM `prices_changes` WHERE id_product = 1;
+------------+---------------------+---------+
| id_product | date                | value   |
+------------+---------------------+---------+
| 1          | 2016-08-11 00:00:00 | 20.50   |
+------------+---------------------+---------+

It should update prices table with last value without product/date duplicates and insert into prices_changes all updates using a trigger.

The problem is that in prices_changes the date column defined as datetime is storing the date value without the time.

How can I store into the prices_changes the data as datetime using the original value?

It seems each time you are inserting data into both tables i.e. price_changes and prices. Reverse process may be helpful.
If possible  try this :

DELIMITER ;;
CREATE TRIGGER `prices_changes_before_insert`
   BEFORE INSERT ON `prices_changes`
   FOR EACH ROW
   INSERT INTO `prices` (`id_product`, `date`, `value`) VALUES (NEW.`id_product`, NEW.`date`, NEW.`value`)
    ON DUPLICATE KEY UPDATE `value` = VALUES(`value`);;
DELIMITER ;

Then

INSERT INTO `prices_changes` SET
   `id_product` = 3,
    `date` = "2016-08-12 14:50:00", # Is a DATETIME value
    `value` = "20.50";


Processing CSV data on import I have an externally-produced CSV filled with geographic points and associated data, but I would like to run some functions on the columns before saving them. For example I need to digest longitude/latitude columns into points, run some numerical processing on other columns, and discarding a couple altogether.

What's the best practise here? Options I've considered:

Processing the data with a shell script before importing. Can be invoked directly from the COPY command with COPY FROM PROGRAM, but still leaves the problem of turning the lon/lat into a geographical type.
Having two tables, data and data_import, and updating data using functions on the columns of data_import.
Having a table where the imported data is as it appears in the CSV, working with it through a View that does the necessary processing.

The common way is to use a staging table (the table you called "data_import" which conventionally would be called "data_stg" or similar variation).
Preferably, no "updating" would be involved (maybe it was just a poor choice of words) but only a single insert into ... select ....


Set up replication and migrate to InnoDB with low downtime Current situation is this:

I have a large legacy DB using only MyISAM tables, the size of whole
DB is 4GiB, one of the tables contains over 10 million rows, so it is
pretty large.
The database is used heavily and downtime should be as low as
possible, no more than 30 minutes (less is better).
All files are not under LVM (so, snapshot is not possible).

I want to set up simple master-slave replication and migrate all tables to InnoDB. What is the best plan for this migration? As I said, it should have low downtime and be as safe as possible

P.S. Currently I have no easily restorable backups, so adding somewhere a step of backing up is a good idea. But I think with master-slave replication backups will be easy. Also, please include what might go wrong during this plan.

Ok,

Here a sample of config to setup your replication :

On Master in your my.cnf add :

server-id                       = 1
log-bin                        = /database/bin-log/mysql-bin
binlog_cache_size              = 16M
slave_compressed_protocol       = 1
binlog_format                  = MIXED
max_binlog_size                 = 100M

On your slave in my.cnf add :

server-id                       = 2
relay-log-space-limit           = 50G
relay-log                       = /database/relay-log/relay-bin
relay-log-index                 = /database/relay-log/relay-bin.index
skip-slave-start
read_only                       = 1

Now you have two solutions, dump or binary copy.

In both case you should made your transformation (myisam to InnoDB) on your slave  slave and switch your slave to master. Also you should active binlogs to catch the master position.

First solution, you can make a dump of your master ( with the master-data option to catch master position) On your .sql dump file, search and replace ENGINE=myisam to InnoDB and load thé dump to thé slave server. You have all tables on InnoDB engine :) after started réplication and  catching up your the late : "second behind master= 0 " it´s time to switch slave to master ( with a new dump (now from slave to master) and take care to switch the conf also).

The second solution is the same but with a cold backup, stop mysql, copy datadir (4gb Will probably done in ~ 10mins dépends on your solution : scp, usb keys...) start your slave and alter each table with engine=innodb. Like thé first one, when it´s done, you should switch master/slave and alter ex-master tables.

Caution with "alter table engine=InnoDB"
 Mysql Will create a InnoDB table and copy data in it so your table size is tablesize x 2 (when innodb table is dobe, mysql drop myisam table)

I'm on my phone so sorry if there are many strange word... :)

(I didn't detailed replication set up cause it's not the subject but we let me know if you want some help.)


Issue with accented characters in SQLiteStudio I downloaded a SQLite 3 dump of Northwind, and opened it in SQLiteStudio. Accented characters aren't displayed nicely. How to solve the issue? I suspect some encoding issue.

> SELECT hex(CompanyName) FROM Customers WHERE CustomerID = 'BOLID';
42F36C69646F20436F6D696461732070726570617261646173

This is not valid UTF-8.

That database was not created correctly.

If you know what the characters are supposed to be, you could manually fix them:

UPDATE Customers SET CompanyName = replace(CompanyName, x'F3', 'ó');

(You'd have to repeat this for all columns and all wrong characters.)


Filtered index throwing error - UPDATE failed because the following SET options have incorrect settings: ANSI_WARNINGS While executing proc1, I am getting error as below:

  UPDATE failed because the following SET options have incorrect
  settings: 'ANSI_WARNINGS'. Verify that SET options are correct for use
  with indexed views and/or indexes on computed columns and/or filtered
  indexes and/or query notifications and/or XML data type methods and/or
  spatial index operations.

I know this is happening because I am having a filtered index on customer table
and that requires SET ANSI_WARNINGS ON.

Even we need have some ANSI options which need to be taken care while
creating filtered index and stored proc. I've handled those things.

And I've fixed the issue by setting ansi_warning on in proc1 which is I think is the ideal solution.

But my question is, even after setting all required ANSI options in proc2, why I am getting this error? Any idea?

Below is my sample code:

create Customers
(
id int,
name varchar(100),
[Stats] BIT NOT NULL DEFAULT 1,
)
go

CREATE NONCLUSTERED INDEX [IX_Customers_Stats]
  ON [dbo].[Customers]([Stats])
  WHERE [Stats] = 0
go

CREATE PROC proc1
as
BEGIN
SET ANSI_WARNINGS OFF;
SET NOCOUNT ON;

....
...
.. some code .....
....
...

EXEC proc2 @customerId

END
go

createPROCEDURE [dbo].[proc2 ]
    @customerId INT
AS
BEGIN

    SET ANSI_WARNINGS ON;
    SET ANSI_NULLS ON;
    SET ANSI_PADDING ON;
    SET ARITHABORT ON;
    SET CONCAT_NULL_YIELDS_NULL ON;
    SET NUMERIC_ROUNDABORT ON;
    SET QUOTED_IDENTIFIER ON;

    UPDATE dbo.Customers set Stats = 0 where id = @CustomerId

END

Possibly because (source):

  ANSI_WARNINGS is not honored when passing parameters in a stored procedure

In any case, you can work around it in two ways:

Stop setting ANSI_WARNINGS to OFF in proc1
Turn ANSI_WARNINGS ON before calling proc2


find table used in SSIS Package script I have a number of SSIS Packages

I am trying to locate a table that is populated by one of the SSIS Package

is there an easy way to find in the project where database.dbo.tabledata has been used in a script within the project

please help team

A low rent approach would be to open a command window (Start -> Run => cmd) and navigate to the folder with all the SSIS packages. cd \my\ssis\folder\

The find command looks for a specific string (case sensitive by default).

C:\my\ssis\folder > find "myCaseSensitiveTableName" *.dtsx

or

C:\my\ssis\folder > find /i "myCaseInsensitiveTableName" *.dtsx

That should be a good starting point for looking through your packages and hopefully the table name(s) you're looking for do not collide with the internal xml of an SSIS package. If that's the case, and you can assume OLE DB Destinations, a find for OpenRowset should be beneficial.


How to fix high memory usage of MariaDB? I am using MariaDB(10.1.21) with following storage engines:

InnoDB
TokuDB
Spider Engine

System Configuration is :

3GB RAM
Dual Core Processor

I have tried pt-mysql-summary tool to identify the memory usage and it shows 90% of innodb buffer pool size is filled,when no process is running on server.

I have also tried restarting the MariaDB server and this reduces memory for about 1-2 hours only and after that it eats up memory.

Any clue why it is consuming too much memory?

Sharing my configurations below:

My.cnf

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
#performance_schema=ON
tmp_table_size=16M
max_heap_table_size=16M
skip-log-bin
#table_open_cache                =16384
#table_definition_cache          =16384

#
# * Basic Settings
#
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc_messages_dir = /usr/share/mysql
lc_messages     = en_US
skip-external-locking

#bind-address           = 127.0.0.1
#skip-networking

# SAFETY #
max_allowed_packet= 16M
max-connect-errors = 1000000
skip-name-resolve
#sql-mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY

# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 500
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 1024
table-open-cache               = 2048

# LOGGING #
log_warnings            = 2
slow_query_log          =1
log-output              = TABLE
long_query_time = 5
log_slow_verbosity      = query_plan
log-error               =/var/log/mysql/system_error.err

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 128M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 1720M

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completion

[isamchk]
key_buffer              = 16M

TokuDB.cnf

plugin-load-add=ha_tokudb.so
tokudb_data_dir=/var/lib/mysql/toku_db_data
tokudb_read_block_size=64k
tokudb_row_format=tokudb_zlib
tokudb_directio=on

Spider.cnf

[mariadb]
#spider_internal_limit          =1
spider_direct_order_limit       =1
spider_skip_default_condition   =1
spider_casual_read              =1
spider_bgs_mode                 =2
spider_direct_dup_insert        =1
spider_auto_increment_mode      =2
#optimizer_switch='engine_condition_pushdown=on'
#optimizer_switch='mrr=on,mrr_sort_keys=off'

It's not critical So don't worry It's a normal Behaviour of Innodb Engine.

If your data size is bigger than or equal the Buffer pool size, Innodb will fill all of buffer size at warm up time. So when you start MySQL after some hours buffer pool will full (Only Buffer pull Not all Ram's capacity)

You'are using multiply Engines in your system. It can complicate config, because you have to controll important variables (such as Memory Configs) of each Engine.

Do you have a reason to use the TokuDB? I think it's better to use one of these engines (InnoDB or TokuDB)

So if you prefer to use innodb, you can set buffer pool size to about 70% of RAM unless your data/index size is small


Getting DPM Alerts for Failed Recovery Point for Database that has Been Deleted Deleted a defunct database in SharePoint, now getting alerts from DPM saying:

The following alert occurred at 2/16/2016 12:16:54 PM.

Status: Active
Data source type:
Data source: SharePointServer\Database_I_Just_Deleted
Computer: SharePointServer
Description: Last 1 recovery points not created.

For more information, open DPM Administrator Console and review the alert details in the Monitoring task area.

Cannot find where to disable monitoring of this deleted database.

DPM is checking the consistency of the SharePoint meta-base, which is not consistent anymore due to you deleting the database in SQL Server. That is, you have removed the logical/physical database in SQL Server but you have not removed references to it from the SharePoint metadata. My advice to you is to restore the database that you deleted and then remove it correctly via the SharePoint PowerShell cmdlets.

There is a really good article on removing orphaned databases here:

http://social.technet.microsoft.com/wiki/contents/articles/30558.sharepoint-2013-deleting-orphaned-sharepoint-databases.aspx

And a list of all database-related cmdlets can be found here:

https://technet.microsoft.com/en-us/library/ee906544.aspx


Need only single table from ibdata1 file I currently have a corrupt database on my live site, because a single Table that has gone missing.

Table 'test1.catalog_product_index_price' doesn't exist

I logged onto my server and downloaded a compressed backup file "/backup/2018-02-03/system/dirs/_var_lib_mysql_.tar.gz"

Once i extracted it locally I noticed ibdata1 file, and this would be my first time trying to work with this type of file.

I only need to recover a single table from the "test1" database. How can I go about exporting this table using the ibdata1 file?

Thanks

I would put the whole backup file onto a testserver, is that possible for you? And then do:
mysqldump -u root -p test1 catalog_product_index_price > dump_catalog_product_index_price.sql


SQL Server 2016 stretch database - namespace error I'm using my MSDN and using the 'adventureworks' db (running SQL Server 2016) and running the task to enable a stretch database but running into an error.  I've logged into Azure with my MSDN account and have setup a few VM's etc. so appears my account works.   From my workstation I go to setup the stretch database with my MSDN azure credentials but getting this error:

  Provisioning of Azure Sql Server stretchserver-XXXXX--adventurewor-20160720-143153189 in resource group stretchgroup-spodev150697-sql2016-westus2 failed.
  Details : {"error":{"code":"MissingSubscriptionRegistration","message":"The subscription is not registered to use namespace 'Microsoft.Sql'."}}

Any thoughts on what steps I need to take to get this registered to work?

I had the same thing happening to me and my students during a SQL Server 2016 hands-on lab a couple of months ago. It is a weird error that shouldn't happen (it's definitely a bug that you should report).

However, there's a workaround: create an Azure SQL Database manually, then delete it. From that moment on, you will no longer receive the error message.


Is it a common pattern in having two AlwaysOn Availability Groups for DR and Reporting? We have a primary OLTP server in the cloud.

We intend to have Synchronous Alwayson Availability Group for Disaster Recovery,
and another Availability Group AAG for Ad-hoc Query Reporting.
This way if our Primary Server goes down, there will not be contention between our Second DR server and Reporting queries. Reporting will be a third node. We will not require different schemas or indexes on the Reporting Server. Do you see any flaws with this design pattern or is it generally common practice?

This would be one AG with 3 replicas, not two separate AGs.

In general is fine, but probably overkill.  Normally if you have an FCI and an AG replica for DR you would use the DR node for reporting.  In a disaster you wouldn't have a separate reporting replica, but disasters are, you know, bad.

For Quorum, a cluster with a single FCI should normally use Node and Disk Majority (or Node and File Share if the databases are stored on SMB shares).  Neither of the other nodes should have a vote.

See WSFC Quorum Modes and Voting Configuration (SQL Server)


Handling case-insensitive queries with case-sensitive data What are some practices for designing a table, or queries, when it comes to case-sensitive data and case-insensitive queries?

For example, I have a Products table that holds household goods, and presumably because I intend to display this data on a client, I would like to have the original names.

If one of the products is called "Vacuum Cleaner" and someone's doing a search through the database for vacuums, I wouldn't want a term like "vacuum" to not find this particular vacuum.

Currently I am using Postgres so I could probably get away with the ILIKE operator, but if a database system didn't support this kind of operator, what would be some alternatives?

SQL text and string data has a concept called "collation". Collations define how character sets compare and sort. Almost every database out there has the concept of "case-sensitive collation" vs. "case-insensitive collation". The collation used for comparison, search and sort can be changed on-the-fly (at a significant runtime cost usually) on a per-query basis. More often the collation is chose during development and defined at the database/table/column level and then the engine will use the chosen collation to do search/comparison/sorting of strings/text.

The specifics of how you define the collation to use will depend on your platform of choice.


postgres: could not find the database system If I try to run the postgres command, I get:
2020-12-29 00:14:53.213 GMT [34374] LOG:  skipping missing configuration file "/usr/local/pgsql/data/postgresql.auto.conf"
postgres: could not find the database system
Expected to find it in the directory "/usr/local/pgsql/data",
but could not open file "/usr/local/pgsql/data/global/pg_control": No such file or directory

How can I find out what config file is telling postgres to look at /usr/local/pgsql/data?
In my case, postgres was installed on OSX via Homebrew.

While I don't know what specific config file is pointing to that location, it can be specified when you run the initdb command, which it's sounding like you may not have run yet, given the error you're seeing.
If you just installed a new/upgrade postgres instance, you'll probably want to use a new data directory from whatever your old instance was using (i.e. you can just run the initdb command with no additional params, and it will use the default /usr/local/pgsql/data location which, apparently, is not yet in use for your machine), then you can export/import the old data if you still need it.


Nested writeable CTEs in PostgreSQL 10? Can I nest writeable CTEs?

WITH "parent_cte" AS (
  WITH "child_cte" AS (
     INSERT INTO ....
  )
  ...
)
...

I know that I can refer to a CTE in the FROM clause of another CTE.

No, modifying CTEs still have to be at the top level.

Testing at Postgres 10.0 produces the error:

  WITH clause containing a data-modifying statement must be at the top level.

select version() ;
                                                    version
----------------------------------------------------------------------------------------------------------------
 PostgreSQL 10.0 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit
(1 row)

x=# create table t (id int, x int) ;
CREATE TABLE
x=# insert into t (id, x) select i, i*2 from generate_series(1,10) as g(i) ;
INSERT 0 10
x=# select * from t ;
 id | x
----+----
  1 |  2
  2 |  4
  3 |  6
  4 |  8
  5 | 10
  6 | 12
  7 | 14
  8 | 16
  9 | 18
 10 | 20
(10 rows)

x=# with a as
      ( with b as
          ( update t set x = x + 3 returning *
          )
        select * from b
      )
    select * from a ;
ERROR:  WITH clause containing a data-modifying statement must be at the top level
LINE 2:   with b as (update t set x = x + 3 returning *) select * fr...
               ^
x=#


Running materialized view refresh in parallel I have a large materialized view that is refreshed periodically (complete refresh) and I would like to do it in parallel. I can already run the query part in parallel but the problem is (much slower) delete in the beginning of refresh.

I have done the following:

set the table and query degree to 6
alter session enable parallel dml;
run refresh with option "parallelism => 6"

But delete still runs serially. Is there some option I have missed?

Set atomic_refresh to false. It will truncate your MV table instead of deleting records.


Limit number of simultaneous queries by user I am looking for way to allow one user to execute maximum 3 queries at the time. Is there such possibility in Postgres?

Due to some technial difficulties I cant limit number of connections, and the solution should be based on limit of simultaneus queries or something similar.

This is not possible now. You have two classes of configuration options

Connections and Authentication, includes things like max_connections
Session/Client Configuration, includes things like statement_timeout, lock_timeout, idle_in_transaction_session_timeout
Role-based settings includes CONNECTION LIMIT (per user).

That's all that the server provides. In order to track what you're talking about the server would have to check pg_stat_activity. You can however get the information you're looking for manually..

SELECT usename AS username, count(*) AS concurrent_statements
FROM pg_stat_activity
WHERE state = 'active'
GROUP BY usename;

You can run a function periodically that checks the above and calls pg_cancel_backend(pid int), or pg_terminate_backend(pid int). See the docs here for more information I don't particularly think that's a good idea. I would work with the default options before I tried to roll my own system based on this.


Update Missing Data In Table I have a table with sample data set-up like below.  How can I update the missing data for the field rglname?  There will always only be ONE rglname assigned to region so I am not worried about duplicate values.  Below is DDL

Create Table #BlueBell
(
  ID int
  ,region varchar(100)
 ,rglname varchar(500)
)

Insert Into #BlueBell VALUES
(1, 'East', 'Bob')
,(2, 'East', NULL)
,(3, 'East', NULL)
,(4, 'West', 'Sam')
,(5, 'West', NULL)
,(6, 'South', NULL)
,(7, 'South', NULL)
,(8, 'South', NULL)
,(9, 'South', 'Mitch')

And desired output is:

  ID    Region  RGLName
  1   East    Bob
  2   East    Bob
  3   East    Bob
  4   West    Sam
  5   West    Sam
  6   South   Mitch
  7   South   Mitch
  8   South   Mitch
  9   South   Mitch

You can use the following update.

update #BlueBell set rglname = b.rglname
from #BlueBell a
inner join (
            select region, rglname
            from #BlueBell where rglname is not null
            ) b on a.region = b.region

Check your desired output here

select *
from #BlueBell


Time series data for ad platform I am trying to figure out how to store time series data for an ad platform I am working on.

Basically I want to know some strategies/solutions for storing billions of rows of data so that I can easily search it (about 6-8 indexes on the table) and get fast counts based on queries.

I tried mySQL with the tokuDB engine and this seems to be very fast but is extremely slow when I try to do a COUNT query when the rows reached about 5-8 million.

I was looking at some noSQL alternatives but since I want to be able to search this data this is probably not the best solution. I was using dynamoDB. I would have had to store the data is many places in order to account for all the searching on the data.

What I am storing is a row in the database for each click on an AD that occurs. This table will grow very fast, especially when this site gets large.

Another solution would be to separate this data per advertiser. This means each advertiser will have their own table where all their data goes into. This means it will be much smaller and the COUNT queries will be much faster. I can even split it up by advertiser and month.

My goal is to give an advertiser the ability to search and display in a paginated way all their clicks. They should be able to get data between a time period and filter by about 5-8 other indexes if they want to.

Did you try sphinxsearch (http://sphinxsearch.com/)?
It is search engine, but not only. It can query very fast with group by, order, filters. Mysql should be good for fast writes with per-table and per-host sharding.

You can split your sphinx index to several parts and use distributed search:

http://sphinxsearch.com/docs/2.2.2/distributed.html

Partitioning is done manually. You should

setup several instances of Sphinx programs (indexer and searchd) on different servers;
make the instances index (and search) different parts of data;
configure a special distributed index on some of the searchd instances;
and query this index.

When searchd receives a query against distributed index, it does the following:

connects to configured remote agents;
issues the query;
sequentially searches configured local indexes (while the remote agents are searching);
retrieves remote agents' search results;
merges all the results together, removing the duplicates;
sends the merged results to client.


SQL Server 2012 DBCC memorystatus has wrong information. How can I correct it? I have a Windows 7 64-bit machine with 16GB ram, 24GB pagefile, 500GB HD and SQL Server 2012 Standard Edition 64-bit running on it.

With 5 minutes of starting up, it routinely uses all my virtual memory and triggers warnings on my machine i.e. ProcessExplorer says sqlservr.exe is using Private Bytes of 38GB, Virtual Size of 61GB.

I believe the issue results from mis-information that I can see when I run DBCC MemoryStatus command, which indicates that the Available Virtual Memory is 8,763,135,270,912. This number is at least 8TB. Other stats returned by the command appear to be accurate.

Does anyone know how the DBCC MemoryStatus command gets this number and how I might correct it? I think this would affect how much virtual memory it would try to allocate.

x86-64 Virtual Address Space Details:

Although virtual addresses are 64 bits wide in 64-bit mode, current implementations (and all chips known to be in the planning stages) do not allow the entire virtual address space of 264 bytes (16 EB) to be used. This would be approximately four billion times the size of virtual address space on 32-bit machines. Most operating systems and applications will not need such a large address space for the foreseeable future, so implementing such wide virtual addresses would simply increase the complexity and cost of address translation with no real benefit. AMD therefore decided that, in the first implementations of the architecture, only the least significant 48 bits of a virtual address would actually be used in address translation (page table lookup).1
In addition, the AMD specification requires that bits 48 through 63 of any virtual address must be copies of bit 47 (in a manner akin to sign extension), or the processor will raise an exception.1 Addresses complying with this rule are referred to as "canonical form."1 Canonical form addresses run from 0 through 00007FFF'FFFFFFFF, and from FFFF8000'00000000 through FFFFFFFF'FFFFFFFF, for a total of 256 TB of usable virtual address space. This is still approximately 64,000 times the virtual address space on 32-bit machines.
[...]
The first versions of Windows for x64 did not even use the full 256 TB; they were restricted to just 8 TB of user space and 8 TB of kernel space. Windows did not support the entire 48-bit address space until Windows 8.1

There you have it. As the OS supports more than the default 8TB VA, it has become necessary to report in the DBCC MEMORY_STATUS how much is the OS actually supporting. Your OS is still the classical 8TB flavor.


Is there something I can do to make this UPDATE run faster? I have a relation EnumeratedSensorReading that includes a siteid and a quantityid column. I want to replace these two columns with a single sensorid column. I have added this column, and it is currently empty.

The mapping from the (siteid, quantityid) tuple to sensorid is provided in a table SiteQuantitySensor. The UPDATE statement is as follows:

UPDATE EnumeratedSensorReading
  SET sensorid = s.sensorid
  FROM EnumeratedSensorReading t
  JOIN SiteQuantitySensor s USING(siteid, quantityid);

The EnumeratedSensorReading table has about 600K rows and the query seems to run forever. Here is the EXPLAIN output:

EXPLAIN UPDATE EnumeratedSensorReading
  SET sensorid = s.sensorid
  FROM EnumeratedSensorReading t
  JOIN SiteQuantitySensor s USING(siteid, quantityid);
                                                  QUERY PLAN
---------------------------------------------------------------------------------------------------------------
 Update on enumeratedsensorreading  (cost=15189729.65..4880750658.10 rows=304673547171 width=44)
   ->  Merge Join  (cost=15189729.65..4880750658.10 rows=304673547171 width=44)
         Merge Cond: ((t.quantityid = s.quantityid) AND (t.siteid = s.siteid))
         ->  Sort  (cost=96208.18..97767.86 rows=623872 width=14)
               Sort Key: t.quantityid, t.siteid
               ->  Seq Scan on enumeratedsensorreading t  (cost=0.00..14828.72 rows=623872 width=14)
         ->  Materialize  (cost=15073092.05..15328879.57 rows=51157504 width=46)
               ->  Sort  (cost=15073092.05..15200985.81 rows=51157504 width=46)
                     Sort Key: s.quantityid, s.siteid
                     ->  Nested Loop  (cost=0.00..654299.55 rows=51157504 width=46)
                           ->  Seq Scan on enumeratedsensorreading  (cost=0.00..14828.72 rows=623872 width=28)
                           ->  Materialize  (cost=0.00..2.23 rows=82 width=18)
                                 ->  Seq Scan on sitequantitysensor s  (cost=0.00..1.82 rows=82 width=18)

As I have very little experience in interpreting these results, could someone help me understand why the statement is so slow, and if there's anything I should do?

In addition to being slow, the statement's results will not be what you expect them to (which causes it to be slow):

From the PostgreSQL documentation:

  from_list A list of table expressions, allowing columns from other tables to appear in the WHERE condition and the update expressions. This is similar to the list of tables that can be specified in the FROM Clause of a SELECT statement. Note that the target table must not appear in the from_list, unless you intend a self-join (in which case it must appear with an alias in the from_list).

AFAICT you are updating each row of enumeratedsensorreading with values obtained by performing an unqualified join (no WHERE-clause) of enumeratedsensorreading against (a qualified join of enumeratedsensorreading against sitequantitysensor (FROM-clause)). This will result in at least 600k updates per row (depending on whethe the relevant values in sitequantitysensor are distinct).

Simply removing the second join should do the trick:

WITH sensor_ids AS
  (SELECT DISTINCT sensorid, siteid, quantityid FROM sitequantitysensor)
UPDATE enumeratedsensorreading e
  SET sensorid = s.sensorid
  FROM sitequantitysensor s
  WHERE s.siteid = e.siteid AND s.quantityid = e.quantityid;

The join you intended is already defined by FROM- and WHERE-clause


php/mysql database redesign and migration: changing databases to tables I wrongly designed my application to have one database to each user. each user had 3 similar tables. I now want to have one database and 3 tables only; where i will use the database name in the old databases as a reference in the new system. There was another database called "users" in the old database that stored the database names. I'm done with the schema design of the new database and now left with the migration.

The trick here is that I have 3 db connections. I first connect to the users database and userinfo table, pick up the database_name in a loop, use it to connect each old db and further connect to personal, accounts and games table.

After picking up the tables, i will like to populate/join it with the new Database (DATA_ONE) and the tables whiles i append the old database_name to the new tables. Any help on this or is there a better way to do this? Thanks much

<?php

$dbhost = "localhost";
$dbname = "users";
$dbuser = "root";
$dbpass = "*****";

$conn1 = mysql_connect($dbhost, $dbuser, $dbpass, TRUE) or die("MySQL Error: " .  mysql_error());
$conn2 = mysql_connect($dbhost, $dbuser, $dbpass, TRUE) or die("MySQL Error: " . mysql_error());
$conn3 = mysql_connect($dbhost, $dbuser, $dbpass, TRUE) or die("MySQL Error: " . mysql_error());

mysql_select_db($dbname,$conn1) or die("MySQL Error: " . mysql_error());

$query  = "SELECT database_name FROM userinfo";
$result1 = mysql_query($query,$conn1);
while($row = mysql_fetch_array($result1, MYSQL_ASSOC))
{
$database_name =$row['database_name'];
$conn2 = mysql_connect($dbhost, $dbuser, $dbpass) or die("MySQL Error: " . mysql_error());
  $db = mysql_select_db($database_name ,$conn2) ;
     // now, pick personal, accounts, games tables of user and populate new database and    tables.
  // this is where i need help.
    }

?>

Instead of Migration to another System, try doing it in place, then mysqldump DATA_ONE and import that mysqldump into the new server.
First, let's create DATA_ONE
DROP DATABASE IF EXISTS DATA_ONE;
CREATE DATABASE DATA_ONE;

Next, Create DATA_ONE tables using the template from database aa123
CREATE TABLE DATA_ONE.personal LIKE aa123.personal;
CREATE TABLE DATA_ONE.accounts LIKE aa123.accounts;
CREATE TABLE DATA_ONE.games    LIKE aa123.games;

Now, Create a UserInfo Table
CREATE TABLE DATA_ONE.UserInfo
(
    userid INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(32),
    last_update DATETIME NOT NULL,
    PRIMARY KEY (userid),
    KEY (username)
);
INSERT INTO DATA_ONE.UserInfo (username,last_update)
SELECT database_name,last_update FROM users.userinfo;

Then, add the database_name column (and index it) to the DATA_ONE tables
ALTER TABLE DATA_ONE.personal ADD username VARCHAR(32);
ALTER TABLE DATA_ONE.personal ADD userid INT;
ALTER TABLE DATA_ONE.accounts ADD userid INT;
ALTER TABLE DATA_ONE.games    ADD userid INT;
ALTER TABLE DATA_ONE.personal ADD INDEX (username);
ALTER TABLE DATA_ONE.personal ADD INDEX (userid);
ALTER TABLE DATA_ONE.accounts ADD INDEX (userid);
ALTER TABLE DATA_ONE.games    ADD INDEX (userid);

OK, that makes the new DATA_ONE database.
Now comes the adventurous part: Loading all those tables into DATA_ONE.
Run these three queries to formulate the SQL needed for migration
SELECT CONCAT('INSERT INTO DATA_ONE.personal (address,password,username,userid) SELECT address,password,'''username''',',userid,' FROM ',username,'.personal;') FROM DATA_ONE.UserInfo;
SELECT CONCAT('INSERT INTO DATA_ONE.accounts (amount,bank,userid) SELECT amount,bank,',userid,' FROM ',username,'.accounts;') FROM DATA_ONE.UserInfo;
SELECT CONCAT('INSERT INTO DATA_ONE.games (game_name,userid) SELECT game_name,',userid,' FROM ',username,'.games;') FROM DATA_ONE.UserInfo;

Here is everything mentioned above shell-scripted and executed:
SQLFILE=/root/MakeDATA_ONE_Database.sql
echo "DROP DATABASE IF EXISTS DATA_ONE;" > ${SQLFILE}
echo "CREATE DATABASE DATA_ONE;" >> ${SQLFILE}
echo "CREATE TABLE DATA_ONE.personal LIKE aa123.personal;" >> ${SQLFILE}
echo "CREATE TABLE DATA_ONE.accounts LIKE aa123.accounts;" >> ${SQLFILE}
echo "CREATE TABLE DATA_ONE.games    LIKE aa123.games;" >> ${SQLFILE}
echo "CREATE TABLE DATA_ONE.UserInfo (userid INT NOT NULL AUTO_INCREMENT,username VARCHAR(32),last_update DATETIME NOT NULL,PRIMARY KEY (userid),KEY (username));" >> ${SQLFILE}
echo "INSERT INTO DATA_ONE.UserInfo (username,last_update) SELECT database_name,last_update FROM users.userinfo;" >> ${SQLFILE}
echo "ALTER TABLE DATA_ONE.personal ADD username VARCHAR(32);" >> ${SQLFILE}
echo "ALTER TABLE DATA_ONE.personal ADD userid INT;" >> ${SQLFILE}
echo "ALTER TABLE DATA_ONE.accounts ADD userid INT;" >> ${SQLFILE}
echo "ALTER TABLE DATA_ONE.games    ADD userid INT;" >> ${SQLFILE}
echo "ALTER TABLE DATA_ONE.personal ADD INDEX (username);" >> ${SQLFILE}
echo "ALTER TABLE DATA_ONE.personal ADD INDEX (userid);" >> ${SQLFILE}
echo "ALTER TABLE DATA_ONE.accounts ADD INDEX (userid);" >> ${SQLFILE}
echo "ALTER TABLE DATA_ONE.games    ADD INDEX (userid);" >> ${SQLFILE}
mysql -u... -p... -ANe"SELECT CONCAT('INSERT INTO DATA_ONE.personal (address,password,username,userid) SELECT address,password,'''username''',',userid,' FROM ',username,'.personal;') FROM DATA_ONE.UserInfo;" >> ${SQLFILE}
mysql -u... -p... -ANe"SELECT CONCAT('INSERT INTO DATA_ONE.accounts (amount,bank,userid) SELECT amount,bank,',userid,' FROM ',username,'.accounts;') FROM DATA_ONE.UserInfo;" >> ${SQLFILE}
mysql -u... -p... -ANe"SELECT CONCAT('INSERT INTO DATA_ONE.games (game_name,userid) SELECT game_name,',userid,' FROM ',username,'.games;') FROM DATA_ONE.UserInfo;" >> ${SQLFILE}
mysql -u... -p... -A < ${SQLFILE}

Now open mysql-workbench or phpmyadmin or whatever you browse data with and look through the DATA_ONE database.
If you are satisfied with what is there, just do this:
mysqldump -u... -p... --databases DATA_ONE > /root/DATA_ONE.sql

Move the DATA_ONE.sql file over to the new server and execute the script.
That's it. Pure SQL solution. No PHP.  If anything does not work, it is probably because I do not know the layout and current contents of the personal, account, & games tables and made assumptions on data. Please post the following in your question:
SHOW CREATE TABLE aa123.personal\G
SHOW CREATE TABLE aa123.accounts\G
SHOW CREATE TABLE aa123.games\G

Give it a Try !!!
UPDATE 2012-05-23 13:57 EDT
Oh, you have 2500 databases? My above solution should still work.
I do have an alternative suggestion: Try to mysqldump all databases into individual files.
Here is a shell script that will dump all 2500 databases into separate files and gzipped
MYSQL_CONN="-uusername -ppassword"
SQLSTMT="SELECT DISTINCT table_schema FROM information_schema.tables"
SQLSTMT="${SQLSTMT} WHERE table_schema NOT IN"
SQLSTMT="${SQLSTMT} ('information_schema','mysql','performance_schema')"
mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" > /tmp/ListOfDatabases.txt
MYSQL_CONN="-uusername -ppassword"
MYSQLDUMP_OPTIONS="--hex-blob --routines --triggers"
COMMIT_COUNT=0
COMMIT_LIMIT=50
for DB in `cat /tmp/ListOfDatabases.txt`
do
    mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} --databases ${DB} | gzip > ${DB}.sql.gz &
    (( COMMIT_COUNT++ ))
    if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ]
    then
        COMMIT_COUNT=0
        wait
    fi
done
if [ ${COMMIT_COUNT} -gt 0 ]
then
    wait
fi

Once you execute this, all databases will be mysqldump'd. For example, the database aa123 will be dumped and gzipped to aa123.sql.gz. This and all the other 2499 mysqldumps can be copied over to the new DB server. Place the dumps in /root/mysqldata on the new DB server. Once they are on the new DB server, you can then unzip all files like this:
cd /root/mysqldata
for X in `ls *.sql.gz` ; do echo ${X} ; gzip -d ${X} ; done

OK this will unzip all the files.
Now, run this to load the SQL scripts into mysql on the new server
cd /root/mysqldata
for X in `ls *.sql` ; do echo ${X} ; mysql -u... -p... -A < ${X} ; done

That's it. No PHP involved. All iteration is handled in the shell script I provided. You can then combine them in the new DB Server as DATA_ONE like I mentioned before.
Give it a Try !!!


How to send Email from a SQL Job with results of multiple select statements I want to create a SQL job and send an emails with multiple select statements including in query results. Is it possible with SQL Job or should I use SSIS?

Edit 1:

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Adventure Works Administrator',
    @recipients = '****@abc.com',
    @Execute_query_database = 'AdventureWorks',
    @query = 'SELECT COUNT(*) FROM AdventureWorks.Production.WorkOrder
                  WHERE DueDate > '2018-03-30'
                  AND  DATEDIFF(dd, '2004-04-30', DueDate) < 2
              SELECT * FROM NorthWind.Production.Work
                  WHERE DueDate > '2018-04-30'
                  ' ,
    @subject = 'Work Order Count',
    @attach_query_result_as_file = 1 ;

  Error message: File attachment or query results size exceeds allowable
  value of 1000000 bytes.

Edit 2: I feel that SQL agent job not pointing to desired DB and sp_send_email has no database name. so I added @Execute_query_database = 'AdventureWorks' but still there is no improvement because in SQL SELECTS using two different databases.

Any thoughts?

You are basically sending 1GB file from sql server which is max. You can reconfigure the default using

  exec msdb.dbo.sysmail_configure_sp 'MaxFileSize','2000000'-- 2GB

I would say, use bcp out the file, compress it using zip or 7zip and then send using powershell or ssis.


Cannot open `pg_hba.conf` in pgAdmin III I am running:

version
-----------------------------------------------------------------------------------------------------
PostgreSQL 9.4.11 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit
(1 row)

and:

pgAdmin III 1.22.2

on the same:

Description: Ubuntu 14.04.5 LTS
Release: 14.04

I can open the postgresql.conf configuration file with pgAdmin but not the pg_hba.conf configuration file.

Both files are in the same directory (/etc/postgresql/9.4/main):

-rw-r----- 1 postgres postgres  4641 Jun 10  2016 pg_hba.conf
-rw-r--r-- 1 postgres postgres 21501 Jun 10  2016 postgresql.conf

but I have noticed that the two configuration files have different permissions. I haven't modified the permissions of pg_hba.conf to make them different from those of postgresql.conf.

Are the permissions of these files different by default? Is there are reason why the pg_hba.conf has more restricted read permissions? Can I go ahead and modify the permissions of pg_hba.conf?

It's not clear what is the point in doing that, but if you're an admin you can do chmod o+r pg_hba.conf to let a non-admin read it.

There are two ways in pgAdmin3 to access those files:

by reading the file system (if it runs on the same host than the server). This corresponds to the File->Open *.conf commands. With that method, permission-wise, pgAdmin is not special, it's like a text editor or a pager.
through the DB connection, with Tools->Server configuration. This requires the adminpack contrib module installed server-side. In the case of Ubuntu, I'm not sure it would work because the configuration files are not inside the data directory below /var, they're under /etc/... and the server-side functions involved are not supposed to reach anything outside the data directory. But if/when this method does work, it's the postgres process that reads the files, so the permissions shown in the question are good enough.


LOB in frequently queried table or separate table I have a table named FinTrans in an Microsoft SQL Server 2012 server with approx 10K-30K records at any given time and about 2K records are inserted and deleted each day.

The table has 3 nvarchar(max) columns that hold possible error messages (3 different types of messages, hence the separate columns) for a transaction. Whenever the contents of the table are listed, these nvarchar(max) columns are also read. The columns are nvarchar(max) as their contents may go beyond 4K, however chances are slim (perhaps with one record every week).

Under these circumstances is it better to store this column in a separate table with 1:1 relationship to the FinTrans table, or shall I just leave the column where it is?

is it better to store this column in a separate table with 1:1 relationship to the FinTrans table, or shall I just leave the column where it is?

Leave it. That's tiny, and not worth adding complexity.  Also, even if both the table and the LOBs were larger, the answer is still to leave it.  Large LOBs will be moved off of the row, and you can set a configuration option for all the LOBs to be stored off-row.  You can also configure the table for the LOBs to be stored on a separate filegroup.  So there's no reason to change the logical model here.

See:

  large value types out of row

  1 = varchar(max), nvarchar(max),
  varbinary(max), xml and large user-defined type (UDT) columns in the
  table are stored out of row, with a 16-byte pointer to the root.

  0 = varchar(max), nvarchar(max), varbinary(max), xml and large UDT
  values are stored directly in the data row, up to a limit of 8000
  bytes and as long as the value can fit in the record. If the value
  does not fit in the record, a pointer is stored in-row and the rest is
  stored out of row in the LOB storage space. 0 is the default value.

From sp_tableoption


SQL*Plus, @, and relative paths Somehow, it seems that SQL*Plus (at least on Windows) is unable to locate a script with a relative path when called with @@ and when the path starts with a single or double dot.

For example, under x:\some\where I have the following directory structure:

script.sql
main-dir\main-sub-dir
              call-script.sql
              script.sql

That is: two script.sql but at different locations.

The content of script.sql just under x:\some\where is simply

prompt SCRIPT root

while the other script.sql's content is

prompt SCRIPT main-dir/main-subdir

call-script.sql reads

@@script.sql
@ script.sql

expected output

If I start SQL*Plus from x:\some\where and then do a

@main-dir/main-sub-dir/call-scripts

The output will be

SCRIPT main-dir/main-subdir
SCRIPT root

This is expected, since the single @ is supposed to search paths from where SQL*Plus was started and @@ is supposed to search paths from the containing script's directory.

unexpected output

Now, if I change call-scripts.sql so:

@@./script.sql
@ ./script.sql

the double @@ seems to change it's behaviour, in that it searches paths from where SQL*Plus was started, and the output will now be

SCRIPT root
SCRIPT root

which is not what I expected.

Is this behaviour documented somewhere, and more importantly, how do I have to change call-scripts.sql so that it calls relative paths (@@../../other-dir/other-sub-dir/script) correctly?

Yup, this is Bug 2391334 which has been around for long time, and probably will not be fixed in the near future.

One way of working around this is "know" the path for scripts without actually hard coding that path.  To do this in SQLPlus requires a trick - if you try to run a non-existent file, then you'll get an error message that includes the path name.

So here's a demo of that in action.  To mimic your scenario I've got:

c:\temp\demo
   script.sql
   maindir
      subdir
         call_script.sql
         script.sql

What we can do is add some commands to the front of call_script.sql which will pick up the path.  It looks a little odd, but you should not need to change it - its just a fixed thing you paste in

set termout off
spool _path_finder.sql
@@_nonexistent_script.sql
spool off;

var path varchar2(100);
set serverout on
declare
  output varchar2(1000) := regexp_replace(replace(q'{
@_path_finder.sql
}',chr(10)),'.*"(.*)".*','\1');
begin
  :path:=substr(output,1,length(output)-24);
end;
/
col path new_val path
select :path path from dual;
set termout on

What's happening here, is we're running a non-existent script, which returns:

"SP2-0310: unable to open file "path\_nonexistent_script.sql"

so with a little regexp we can extract the path, store it in a SQLPlus variable and then use from that point on.

So the final version of your call_script.sql would look like this

set termout off
spool _path_finder.sql
@@_nonexistent_script.sql
spool off;

var path varchar2(100);
set serverout on
declare
  output varchar2(1000) := regexp_replace(replace(q'{
@_path_finder.sql
}',chr(10)),'.*"(.*)".*','\1');
begin
  :path:=substr(output,1,length(output)-24);
end;
/
col path new_val path
select :path path from dual;
set termout on
prompt path was &path

@@&path\script.sql
@&path\script.sql

and when we run that, we get the following

SQL> @maindir\mainsubdir\call_script
path was maindir\mainsubdir
script in subdir
script in subdir

and there you go :-)


PostgreSQL - How to create a function to validate a field is DateTime? I would like to create a function to validate a field from an import table and ensure the field is in 'DateTime' format. I am not entirely sure how to do this and would appreciate any help?

Function Name: val_datetime
Field: Created Date
Format: dd/mm/yyyy hh:mm:ss

Thank you for any help.

Try something like:

to_timestamp('21/12/2008 12:34:23','dd/mm/yyyy HH:MI:SS')

I assume you want to catch any exception and return null instead, so something like:

create or replace function my_to_timestamp(arg text)
returns timestamp language plpgsql
as $$
begin
    begin
        return to_timestamp(arg, 'dd/mm/yyyy HH:MI:SS');
    exception when others then
        return null;
    end;
end $$;

Example:

postgres=# select my_to_timestamp('21/12/2008 12:34:14');
   my_to_timestamp
---------------------
 2008-12-21 00:34:14
(1 row)

postgres=# select my_to_timestamp('21/12/2008 12:x:14');
 my_to_timestamp
-----------------

(1 row)

You can find all valid/invalid rows as:

with t(s) as ( values ('21/12/2008 12:34:14')
                    , ('21/12/2008 12:Tx:14')
)
select s from T
where my_to_timestamp(s) is [not] null;


What's the best setup for a MySQL multi master replication for a POS in a restaurant? We're in the process of implementing a point of sale (POS) solution where we have heavily modified an existing open source POS. The original POS was never created with replication/synchronization between terminals and we want to add this.

We are using MySQL 5.7 and require a solution to do the following:

each POS terminal needs to operate independently of other terminals or back office server - i.e. have it's own local database and still function, even if the network goes down
multi-master synchronization - all POS terminals should reflect the current state of the system

Example 1: you should be able to start an order on one POS terminal, and finish/modify it on another terminal.

Example 2: If you start an order for a restaurant table on one terminal, another waiter will see that that table is occupied, from another POS terminal.

scalable to n terminals - some customers will have 1 POS terminal, some 3 and some 100. The solution needs to scale to any number of POS terminals

I've looked at the following solutions:

MySQL Cluster
MySQL Replication
Maria DB Replication
Maria DB Gallera Cluster

It seems that MySQL semi synchronous replication could work, but I'm not sure this is the best setup.

I'd like some direction in terms of:

what technology to use
how best to set this up

I would go with Galera.  It is self-healing after a single node dies and comes back online.  If, however, the entire network dies (no machine can talk to any other machine), NDB Cluster may be the only solution since it repairs outages by user-defined "conflict resolution" algorithms.

Plain Replication has a lot of potential failings, and needs more hand-holding.  Semi-sync requires that part of the network stay up.  When semi-sync can't do its "sync" it quitely reverts to "async", thereby losing what you were expecting.

100 nodes in any replication technology will be a challenge.  Going past 5-10 nodes needs special casing for larger networks.  For plain Replication and semi-sync, "relay" servers are desirable; they take extra setup and require extra handling in failure situations.  I have not heard of NDB or Galera being shaken down for larger networks that maybe a dozen nodes.  Some people have multiple Galera clusters with async between the clusters.

You might be better off spending time and money to hardening the network first.

A large department store might have 10 checkout counters, each with 4 POS.  That hints at hard-wired (very reliable) 4-node cluster of some kind, plus something more fragile between the clusters.  If you use wifi, be sure to encrypt all traffic.

With plain Replication, a dual-Master, single-writer (or 3-node Galera cluster) is possibly the best.  It does require that all POS be able to write to the Master, else they are offline.  It also requires extra coding to deal with "critical reads" -- that is where you post something, then need to immediately read it back, and must be sure any replication lag is accounted for.  (Note: I am not having any writes occur on the POS, only indirectly through the Master and Replication.)

Chew on all that.  Sorry, I don't have a "Do this" answer.


Multi-Table Groupwise Max Problem where I need a second field along with the max I have a query I'm having trouble with that involves 3 tables described below. There are more fields that are immaterial to this query.

users:
  id
  display_name
matches:
  id
  league_seasons_id
  date_played
  winner_id
  loser_id
players:
  id
  match_id
  player_id
  nth_match
  division
  result   # values are "Win" or "Loss"
  end_hcp

I'm trying to produce output like this:

  Name        Hcp   Wins   Losses   High Run    HR Pct of Hcp
  Alan        705    12       2       27            3.83%
  Bill        815    10       4       30            3.68%
  Charlie     611     9       5       12            1.96%
  Denise      788     8       6       15            1.90%
  ...

The query below mostly works, except for the Hcp column. In the players table there are 2 records for each match, one with the winner's info and one with the loser's. In each record is the start_hcp and the end_hcp, i.e. the handicap that player had before playing the match and the new one based on the match.

I need the end_hcp from the very last match that each player played up until now (the date/time of the query). I'm guessing there's going to be a subquery involved but I haven't figured out what it is.

SELECT
    p.division AS Division,
    u.display_name AS Name,
    p.end_hcp AS Handicap,
    count(if(result = "Win", 1, NULL)) AS Wins,
    count(if(result = "Loss", 1, NULL)) AS Losses,
    max(high_run) AS "High Run",
    concat(truncate((max(high_run) * 100 / p.end_hcp), 2), '%') AS "HR Pct of Hcp"
FROM
    `lwljhb_lwl_players` AS p,
    `lwljhb_lwl_matches` AS m,
    `lwljhb_users` AS u
WHERE
    m.league_seasons_id = 9 AND
    p.match_id = m.id AND
    p.player_id = u.id AND
    u.display_name <> "Bye"
GROUP BY u.display_name
ORDER BY p.division, Wins DESC, Losses

I'm not a MySQL guy but what about something like the following (for end_hcp)?

Edit: based on syntax comment

SELECT
    p.division AS Division,
    u.display_name AS Name,
    (select max(end_hcp) from lwljhb_lwl_players where player_id = p.player_id and match_id =(select max(p.match_id) from lwljhb_lwl_players where p.player_id = u.id))
 AS Handicap,
    count(if(result = "Win", 1, NULL)) AS Wins,
    count(if(result = "", 1, NULL)) AS Losses,
    max(high_run) AS "High Run",
    concat(truncate((max(high_run) * 100 / p.end_hcp), 2), '%') AS "HR Pct of Hcp"
FROM
    `lwljhb_lwl_players` AS p,
    `lwljhb_lwl_matches` AS m,
    `lwljhb_users` AS u
WHERE
    m.league_seasons_id = 9 AND
    p.match_id = m.id AND
    p.player_id = u.id AND
    u.display_name <> "Bye"
GROUP BY u.display_name
ORDER BY p.division, Wins DESC, Losses


How to drop java source from Oracle database properly? I'm a little stuck here with my Java Source. First I want completly drop it from the database, because I want to recompile it, but I can't drop is, because if I execute this command:

drop java source "SCHEMA.JAVASOURCENAME";

I get this error:

ORA-04043: object JAVASOURCENAME does not exist

The Javasource itself has this structure (this is the plsql/java code for it):

create or replace and compile java source named myjavasource as

import java.io.*;
import some.custom.jar.one.*;
import some.custom.jar.two.*;
import some.custom.jar.three.*;

    public class MyJavaClass
    {
      public static String myjavafunction(String connectString, String identity, String password)
      {
       ...
      }
    }

I executed this "create or replace and compile java source..." command as DBA and with my user as well, I don't know if this can be the problem.

So my question is, how can I drop this java source?

My second question is who can I handle the custom jars? I imported them with this command:

call dbms_java.loadjava('-genmissing -r -v -force -grant SCHEMA ./some.custom.jar.one.jar');

I also executed this as DBA and with my user. If is check the invalid objects:

SELECT *
    FROM user_objects
    WHERE object_type like '%JAVA%'
    AND status = 'INVALID'

as DBA I see the whole world invalid (lot of Java classes), but when I check this as user I only get the javasource and the java class name in the invalid list (I think it needs some more external jars, what I can't find right now).

So, why is the invalid java object list different as DBA and as user? Do I need to execute the Java source creation and the jar imports as DBA (and ONLY with DBA), or with the database user if I want to use it (the java funtion, what is using the external jars) with all schema users?

Thank you very much!

Each element of a name must be quoted individually:

drop java source "SCHEMA"."JAVASOURCENAME";

The identifier "SCHEMA.JAVASOURCENAME" refers to a java source name SCHEMA.JAVASOURCENAME in the current schema, e.g. "VORIAND"."SCHEMA.JAVASOURCENAME"


Check mysql domain that is using resources I have a webserver hosting about 300 websites and sometimes my CPU usage gets close to 100% and when I use "top" in my CENTOS to check who is using all CPU, I discover it's mysql. I would like then to know what website is causing that high mysql CPU usage. With Apache I can know exactly which virtual domain is consuming my cpu, ram, bandwidth... but I have no idea how to do that with mysql. Any idea?

You could check your slow query log. If it's CPU intensive, it may be some heavy hitting queries that take a while to process. Check to make sure your slow query threshold is set to a reasonable amount of time and then look at your log.

When your server is in the middle of a CPU spike, log in and do show full processlist to look at the queries that are running at that moment. Is there a higher number of queries than normal? Is there a high number of slow queries than normal?

You could use show open tables which will show you what's in the table cache, but it doesn't necessarily tell you what is using up all the resources, but rather which databases/tables are the most active.


Are there issues with using SELECT * in a view definition? I am creating a number of proxy views for views in a separate database. To avoid defining the view in two places, I want to use SELECT * to create the proxy view.

I've searched for reasons not to do this and didn't find any. Anyone have an opinion on using SELECT * for a view definition?

Yes, there are reasons to not use SELECT * in a view. The most important is that SQL Server caches the metadata of a view's output, and it doesn't magically update if underlying objects change. Here's a quick example to demonstrate:

USE tempdb;
GO
-- simple table with two int columns
CREATE TABLE dbo.x(a INT, b INT);
GO
INSERT dbo.x(a,b) VALUES(1,2);
GO

-- simple view using SELECT *
CREATE VIEW dbo.v_x
AS
  SELECT * FROM dbo.x;
GO

-- view will not be updated to see these changes:
EXEC sys.sp_rename N'dbo.x.b', N'c', N'COLUMN';
ALTER TABLE dbo.x ADD b DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;
GO

-- view shows wrong data
SELECT * FROM dbo.x;
SELECT * FROM dbo.v_x;
GO

You need to force SQL Server to refresh its view of the view.

EXEC sys.sp_refreshview @viewname = N'dbo.v_x';
GO

-- now view is correct
SELECT * FROM dbo.v_x;
GO

Now why do you want to make an argument for using SELECT * in a view? Because you don't want to have to type the column names a second time? Did you know that you can easily generate the list of columns from Object Explorer by right-clicking and saying Script View/Table As > SELECT To > clipboard? Or by dragging the columns folder onto the query window?

Just don't do it. What you save in typing (and the time you've already wasted debating this) isn't worth the risk and doesn't justify having some exception where you make it seem like SELECT * is okay. It's a bad practice for a multitude of reasons, and going out of your way to make exceptions is inconsistent and can often encourage the wrong thing to junior developers.

In fact I try to remember to create all of my views WITH SCHEMABINDING so that changing them later is a pain. I don't want to be able to change views willy-nilly precisely because that's a surefire way to break something.


Will MyISAM prevent multiple users inserting data at the same time? I am using a MyISAM database to store user data because I am using full text searching. I have looked in to options like Sphinx but cannot install this on my shared hosting plan which uses MySQL 5.5.36.

I have been researching the problems of MyISAM and have come across the full table locking.

By using MyISAM, will this mean that, say if multiple users are on my website and two users are searching and two users are trying to INSERT into the same table that they will be unable to do so?

Is anyone able to explain to me how this works?

Thanks

One of the biggest problems with MyISAM is the low write concurrency. In the most general case, a single write (update, delete or insert) to a single MyISAM table will block all other writes and reads. Multiple reads, however, can happen concurrently.

You can force MyISAM to perform concurrent inserts at the end (not for updates and deletes), but it normally requires a defragmented table. Read more about concurrent inserts for MyISAM on the manual. Multiple writes can always happen to different tables.

InnoDB doesn't have those problems, and also has better memory management, corruption resilience, support for foreign keys and much more features. With InnodB you can write to the same table in concurrency as long as you do not write to the same exact row (disabled to avoid race conditions). It can provide also reads and writes at the same time, even on the same rows thanks to multi-versioning.

If MySQL FULLTEXT is good enough for you, I would recommend you to search for a hosting that provides MySQL 5.6, where InnoDB Fulltext is available. Otherwise, you will have to search for an alternative application, like the mentioned sphinx.


Migrate large (200GB) mysql databases to new hosts with as little downtime as possible My question is about migrating large amounts of MySQL data with as little downtime as possible.

I have 3 MySQL databases that need to be moved from one RDS instance inside a VPC into a brand new AWS account into a new RDS instance.

Each database is around 200GB.

I'd like to minimise downtime as much as possible on the 3 sites these databases support.

The sites are very read and write intensive, so unfortunately I can't go into a read-only mode for a few hours while I migrate.
I cannot SSH into and copy the data files for the mysql instances because this is RDS.
It takes around 4-5 hours to run mysqldump for the entire database to my local machine.

What options do I have to be able to do this and how much downtime would you estimate for any ideas?

If you can tolerate a little downtime you can make a snapshot and share the snapshot with the new account.  This is a simple and cheap approach though you will be down for the time it takes to make the snapshot, share and then restore it and then point your clients to the new instance.

For a solution with much less downtime, if you are using RDS you can make use of AWS DMS (Data Migration Service).  It can be used as a method for either a one-off data migration, or you can use it to replicate data from one VPC to another within the same AWS Account, or even cross account, so that you can replicate data written to the old RDS into the new one, until you are ready to switch to using the RDS in the new account.

Good documentation on AWS DMS can be found here:
https://docs.aws.amazon.com/dms/latest/sbs/DMS-SBS-Welcome.html

If you can, to minimise downtime, set up the current database with a CNAME and have your clients address the RDS via that CNAME.


How to solve database is not mounted SQL>
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00204: error in reading (block 3, # blocks 8) of control file
ORA-00202: control file:
'E:\APP\SHAVULHAMEED\FLASH_RECOVERY_AREA\ORCL\CONTROL02.CTL'
ORA-27070: async read/write failed
OSD-04006: ReadFile() failure, unable to read from file
O/S-Error: (OS 1117) The request could not be performed because of an I/O
device error.

If you have multiple control files then you can try to copy another one over this corrupted one. Otherwise you need to restore it from an (RMAN) backup.