Backup a single table I need to Truncate a table in Sql server which containing around 250GB data but before truncating i need to take backup of this,it may to csv file or other mode. What is best way to do it.

I'd recommend using the BCP utility.  From that documentation:

Copying table rows into a data file (with a trusted connection)

Basic

This example creates a data file named StockItemTransactions_character.bcp and copies the table data into it using character format.

At a command prompt, enter the following command:

bcp WideWorldImporters.Warehouse.StockItemTransactions out D:\BCP\StockItemTransactions_character.bcp -c -T


How to "swap" out production tables I have a MySQL table that holds a few thousand records which go out of date very quickly.  A few times a day we run a slow process which needs to pull data from multiple sources and re-populate the table.

The catch is the table needs to be available all the time.  My initial idea was to do the slow population process on a temp table, then drop the live table and rename the temp table to replace the live table.

Is it possible to lock the live table before the drop/rename process so that queries will be delayed and not lost?

Or is this the wrong approach?

You can do swapsies by specifying both tables in your RENAME statement.

CREATE TABLE new_table (...);
RENAME TABLE old_table TO backup_table, new_table TO old_table;

MySQL documentation on RENAME TABLE says:

  The rename operation is done atomically, which means that no other session can access any of the tables while the rename is running. For example, if you have an existing table old_table, you can create another table new_table that has the same structure but is empty, and then replace the existing table with the empty one...


Data drift on Row-Based-Replication i've realized that one of my mysql slaves has missing rows.
select count on specific table on master:
9605010
on slave:
9593831

slave is completely sync,
no seconds_behind_master,
no errors,
no slow log

version on both master and slaves:
mysql  Ver 14.14 Distrib 5.6.35-80.0, for debian-linux-gnu (x86_64) using  6.3

sync_binlog=1,
row-based-replication

i'm not sure about using pt-table-checksum because, this is a prod system.
What could be cause the data drift on rbr anyway?

It's impossible to tell without investigation. Potentially it could be mistakes when you created the slave, it could be direct writes to the slave, bugs in replication, bugs in an app with SET binlog=0, etc.

Worse, it's very hard to investigate post-factum. You need to prepare for the data drift troubleshooting.

pt-table-checksum would be your first step. It's a mature tool, safe to use on prod, but problems are possible, too. Most likely, locking issues, deadlocks. If need to be extra paranoid, I set --chunk-time to something like 0.1 or even less.

Find differences in the data, review it (you may want to use twindb_table_compare to find which rows are missing/extra/different), and fix the mismatches (with pt-table-sync or rebuild the slave depending how big the data drift is).

And put pt-table-checksum in cron. If one run takes long time (say 1 - 2 days) I recommend to split discovery and alerting parts (i.e. the heavy pt-table-checksum populates percona.checksums and a light script checks percona.checksums and alerts if inconsistencies are found). On my systems I run pt-table-checksum continuously.

Then, when an inconsistency is discovered inspect the data, the binlog and then you'll figure out where it comes from.


Splitting TempDB into multiples files equal to number of CPUs The article SQL Server tempdb Best Practices Increase Performance suggests that I should split tempdb into a number of files equal to the number of cores. So for 4 cores you get 4 files.

  By having the larger number of files, you can increase the number of
  physical I/O operations that SQL Server can push to the disk at any
  one time. The more I/O that SQL Server can push down to the disk
  level, the faster the database will run. With standard databases, SQL
  Server can cache a large amount of the data that it needs into memory.
  Because of the high-write nature of the tempdb, the data needs to be
  written to the disk before it can be cached back up into memory.

Though it sounds good in theory, is it really that good as a general optimisation? Is it something that may only apply for specific systems where IO is very high?

A ratio of 1/4 to 1/2 times the number of TempDB data files to machine cores has long been the recommendation...

  But there's now even better guidance. At PASS in 2011, my good friend Bob Ward, who’s the top guy in SQL
  Product Support, espoused a new formula: if you have less than 8
  cores, use #files = #cores. If you have more than 8 cores, use 8 files
  and if you’re seeing in-memory contention, add 4 more files at a time. [link]

The last sentence has always been relevant. If you're not seeing contention, why add additional files? To play safe, most will add 2-4 files as a starting point for the majority of builds but beyond that, measure and react.


Cannot start Oracle Enterprise 13C after stopping OMS Oracle Software versions installed:

Oracle Grid infrastructure 12C release 2 [12.2.0.1]
Oracle Database 12C release 1 [12.1.0.2.0]
12.1.0.2.0_Database_Template_with_cdbpdb_for_EM13_2_0_0_0_Linux_x64.zip
Enterprise manager 13 [13.2.0.0 with plug-ins]

After successfully installing all of the above packages including the enterprise manager 13C, I decide to stop the enterprise manager services by running the below script:

#!/bin/bash
export ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/db_1
export OMS_HOME=/u01/app/oracle/middleware
export AGENT_HOME=/u01/app/oracle/agent/agent_inst

# Stop everything
$OMS_HOME/bin/emctl stop oms -all
$AGENT_HOME/bin/emctl stop agent
$ORACLE_HOME/bin/dbshut $ORACLE_HOME

Once the enterprise manager 13C services has stopped, I decide to start it again and I get the below error messages:

Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
Starting Oracle Management Server...
WebTier Successfully Started
Oracle Management Server Could Not Be Started
Check EM Server log file for details: /u01/app/oracle/gc_inst    /user_projects/domains/GCDomain/servers/EMGC_OMS1/logs/EMGC_OMS1.out
AdminServer Could Not Be Started
Check Admin Server log file for details: /u01/app/oracle/gc_inst/user_projects/domains/GCDomain/servers/EMGC_ADMINSERVER/logs/EMGC_ADMINSERVER.out
Oracle Management Server is Down
JVMD Engine is Down
Starting BI Publisher Server ...
BI Publisher Server Could Not Be Started. Check BIP Server log file for details.
BI Publisher Server Logs: /u01/app/oracle/gc_inst/user_projects/domains/GCDomain/servers/BIP/logs/
AdminServer Could Not Be Started
Check Admin Server log file for details: /u01/app/oracle/gc_inst/user_projects/domains/GCDomain/servers/EMGC_ADMINSERVER/logs/EMGC_ADMINSERVER.out
BI Publisher Server is Down
Oracle Enterprise Manager Cloud Control 13c Release 2
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
Starting agent ............................................. started.

All I did was stop the enterprise manager 13C Oracle Management Service, nothing else and for some reason I can't start the service again.

If anyone has come across this problem, please help.

After all the searching, I could not find any help, until I figured out the problem myself. In order to use enterprise manager, the pluggable database container must be accessible and open before starting the oracle management service.

This can be done through enterprise manager 12C:

Access your pluggable database containers shown below

You will see that container database is down by open-mode column. Click on actions and say open all or the one you want:

Choose the appropriate action(s) to open your PDB's

Or you could run the below query to open all pluggable databases from sqlplus [I tried it from sqlplus and it did not want to work for me]:

alter pluggable database ALL close IMMEDIATE ;
alter pluggable database ALL open read write;

If it works you should see the open-mode in green shown below in enterprise manager 12C:

After starting the PDB, you should be able to start the Enterprise manager 13C OMS as shown below:

#!/bin/bash
export ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/db_1
export OMS_HOME=/u01/app/oracle/middleware
export AGENT_HOME=/u01/app/oracle/agent/agent_inst

# Start everything
$ORACLE_HOME/bin/dbstart $ORACLE_HOME

$OMS_HOME/bin/emctl start oms
$AGENT_HOME/bin/emctl start agent


How to optimize MySQL for large BLOB updates I have a table that holds large BLOBs (up to 10M), and is updated frequently. The problem is that UPDATE statements can take up to 1 second to execute, and due to app design this blocks UI. I need to speed up those  UPDATE/INSERT statements. Is there a way to do it by tuning MySQL server/storage engine/etc?

The table in question is InnoDB, and I have tried tunring compression on, but that didn't seem to make a lot of difference. Client is on the same machine as server so there is no network overhead. Server is MySQL 5.5

This looks like a job for InnoDB Architecture Tuning !!!

Where can BLOB data become a bottleneck for InnoDB? Three places

Place #1 : InnoDB Log File

The size of the InnoDB Log Files (set by innodb_log_file_size) could be a bottleneck if there are many transactions being written that update BLOB data.

Place #2 : InnoDB Log Buffer

The InnoDB Log Buffer (set by innodb_log_buffer_size) can be a bad bottleneck if it it set smaller that the BLOB itself. The default is 8M. You may need to resize it to accommodate multiple rows that have that BLOBs. Perhaps 128M would be a good place to start. That can also reduce disk I/O when writing to the InnoDB Log Files.

Place #3 : The MySQL Packet

What is a MySQL Packet?

According to the Book

Page 99 Paragraphs 1-3 explains it as follows:

  MySQL network communication code was
  written under the assumption that
  queries are always reasonably short,
  and therefore can be sent to and
  processed by the server in one chunk,
  which is called a packet in MySQL
  terminology. The server allocates the
  memory for a temporary buffer to store
  the packet, and it requests enough to
  fit it entirely. This architecture
  requires a precaution to avoid having
  the server run out of memory---a cap
  on the size of the packet, which this
  option accomplishes.

  The code of interest in relation to
  this option is found in
  sql/net_serv.cc. Take a look at my_net_read(), then follow the call to my_real_read() and pay
  particular attention to
  net_realloc().

  This variable also limits the length
  of a result of many string functons.
  See sql/field.cc and
  sql/intem_strfunc.cc for details.

Anytime there is transmission of MySQL Data, the MySQL Packet channels MySQL Data into the OS and network. Like the InnoDB Log Buffer, the MySQL Packet must accommodate multiple BLOBs as well.

EPILOGUE

I have discussed this topics in the past

Nov 22, 2011 : MySQL query 'going away' on executing INSERT ON DUPLICATE UPDATE statement with a 12524 character blob
Aug 01, 2011 : How does max_allowed_packet affect the  backup and restore of a database?
Apr 27, 2011 : Changed max_allowed_packet and still receiving 'Packet Too Large' error

CAVEAT

Compression may have made things a little worse. Why ?

When compress an InnoDB table, any time data and index pages for that table is read, the page and an uncompressed copy of the page coexist in the InnoDB Buffer Pool. If you want to keep the table compressed, you must expand the InnoDB Buffer Pool (set by innodb_buffer_pool_size). I wrote about this as well as an update to an old answer of mine : innodb_file_format Barracuda

UPDATE 2013-07-19 16:11 EST

Based on your last comment

  Initial impressions: while tuning InnoDB buffers helped a bit, it seems like more improvement came from turning off binary logs for this particular table. Still monitoring the performance...

I have a suggestion

You may need to rearchitect your InnoDB files so that all .ibd files are on the data volume, while other MySQL components (ibdata1, ib_logfile0, ib_logfile1, all binary logs) could be mounted on another disk. I wrote about this before in a post about PostgreSQL : Postgres Write Performance on Intel S3700 SSD, The person asking that question said he got a 10% increase in performance.

UPDATE 2013-07-26 15:33 EST

Since I asked

  QUESTIONS : 1) How much RAM is on the DB Server? 2) What is the result of SHOW VARIABLES LIKE 'innodb_file_per_table';; 3) What do you get when you runSELECT SUM(data_length+index_length) FROM information_schema.tables WHERE engine='InnoDB';?

and you said

  8gb RAM, 4gb available to MySQL. 'innodb_file_per_table', 'ON'. SUM is 3316736000

I recommend the following (if you have not already done so)

Set innodb_buffer_pool_size to 3G
Set innodb_log_file_size to 1G

Give it a Try !!!


SQL Server 2017 Availability Group Deployment - Some availability replicas are not synchronizing data I'm in the process of configuring my first cluster using SQL Server 2017's availability groups.  I have some experience with SQL Server failover clusters, but wanted to migrate to the Availability Group technology.  I have created to Windows 2019 (DB01 and DB02) servers and configured them as a typical Microsoft Cluster.  I have also installed SQL Server 2017 on both servers, using identical configurations.  Data on drive D, logs on drive E, and backups (for now) on drive D:.  So far, so good.  The SQL services are running under a domain account named svcSQL.   I've restarted both servers to verify that the service starts correctly.
I restored my two databases (called Lookup and Demo) on to DB01, and left DB02 empty.  My intent was to let DB01 seed DB02.  Using the Availability Group Wizard, I created a new availability group called Lookup, and added the DB02 server as a replica.  DB02 shows up as a secondary, and I checked the "Automatic Failover" checkboxes for both servers.  I left the "Endpoints" tab with the default values.  All items on the "Backup Preferences" tab were unavailable, so I left them alone.  I added a listener using a DNS name that was already created on the domain controller, and provided its matching IP address.  I selected Automatic Seeding for the Data Synchronization panel.
The validation screen showed all green items, as did the Results panel.  However, the dashboard showed that the availability group was disconnected.  I did a "grant connect on endpoint..." to the SQL service account, which resolved that message.  I have also temporary disabled the Windows firewall on both servers.
At this time, the dashboard shows a critical error and two warnings.  The critical error states that the Availability group is not ready for automatic failover.  That makes sense, as the synchronization process is not complete.
The two warnings are that "Some availability replicas are not synchronizing data" and "Some synchronous replicas are not synchronized."  I gather the first warning is likely a result of the second warning.
I'm wondering if anyone can offer me some pointers on how to resolve this.  The process seemed pretty straightforward, but something is definitely not right.  I've tried creating the group a few times with variations on the steps above, but I seem to wind up with the same results each time.
Thanks in advance for any insights you can provide on this.  I'll gladly provide any additional information that would help resolve this.
Thanks!

It turns out, the process was working normally, but I was missing a step at the end.  Once I had configured the availability group, I didn't know that I then had to connect to the remote server and add the database.  In the tutorials I had found online, none had included that step.  It was a small but significant item.
Once I added the database on the remote server, the synchronization process started and the failover cluster worked as intended.  Thanks to everyone who offered the suggestions on how to resolve this. I'll mark it as user error on my part.  Sigh....


MySQL slow importing on hyper-v virtual server compared to host Database dump file:

6 MB size
14k rows
10 tables

Server specs (Ubuntu 20.04, virtual (hyper-v)):

OS: Ubuntu 20.04.1 LTS
MySQL version: Ver 8.0.21-0ubuntu0.20.04.4 for Linux on x86_64
((Ubuntu))
4 core, 2 GB RAM
100 GB storage

Host MySQL

Windows server
MySQL version: 8.0.17 - MySQL Community Server - GPL
HDD Storage
8 GB RAM

Importing dump to the database on the host machine took 1-3 seconds, importing on the guest server 5-6 minutes.
iotop reports maximum write speed 5 MB/s while importing.
Refreshing database on phpmyadmin, i can see all row is imported after 1 minutes, the rest of the time is just altering table:
ALTER TABLE `table`
   MODIFY `ID` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

Import command:
mysql -u user -p db < /home/user/dump.sql

tried:

mysql-server reinstall on guest machine
disable unique checks with UNIQUE_CHECKS=0;
disable foreign key checks with FOREIGN_KEY_CHECKS=0;

Turning on: "Turn off Windows write-cache buffer" solved the issue for me.
(no restart required for me)

Benchmark write speed, before and after the setting.
dd if=/dev/zero of=/tmp/test2.img bs=512 count=1000 oflag=dsync


mysql how to replace a column content I using Mysql.

Table: Content_T1

id :  int

title : varchar255

content: text

my all page content will be save in content columns.

mysql content column...  there is some <img src="http://abc.com/data/img/......">

But I had change my domain,

so I need change all <img> tag

if url =  http://www.abc.com/data/img/....

I need change to =  http://www.def.com/data/img/....

Just replace this url and path, without affecting other content.

How can I do ?

Let's say you want to update id 27
Run this first
SELECT REPLACE(content,'http://www.abc.com/data/img','http://www.def.com/data/img') newurl
FROM Content_T1 WHERE id = 27;

If the result is correct, you can run
UPDATE Content_T1
SET content = REPLACE(content,'http://www.abc.com/data/img','http://www.def.com/data/img')
WHERE id = 27;
SELECT content FROM Content_T1 WHERE id = 27;

If the result is correct and you are ready to fix all of them, run this query without the WHERE clause:
UPDATE Content_T1
SET content = REPLACE(content,'http://www.abc.com/data/img','http://www.def.com/data/img');

Please, go to a test server and run this. When the results are correct ...
GIVE IT A TRY !!!


Configuring sqlnet.ora and tnsnames.ora on the client side I have an application server talking to an Oracle database, using the Oracle instant client. We are troubleshooting a specific bug, and it was asked if I could add the following flags to to my sqlnet.ora file:

DIAG_ADR_ENABLED=OFF
DIAG_SIGHANDLER_ENABLED=FALSE
DIAG_DDE_ENABLED=FALSE

Prior to this request, we did not need a sqlnet.ora file on the client side, with instant client, we could reference the classpath of ojdbc6.jar and the ld_library_path of the directory of the jar, and use a URL like this:

jdbc:oracle:oci:@server:port:sid

Looking at the Oracle Docs, it appeared that I could reference a sqlnet.ora file and/or a tnsnames.ora file on the client side if I added environment variables for ORACLE_HOME and TNS_ADMIN.

I created the sqlnet.ora file with the parameters above, and added some trace parameters so that I could verify that the file was being read:

TRACE_LEVEL_CLIENT=10
TRACE_DIRECTORY_CLIENT=/tmp

I also created a tnsnames.ora file:

APPDB =
    (DESCRIPTION =
        (ADDRESS LIST =
                (ADDRESS = (PROTOCOL = TCP)(HOST = *host*)(Port = *port*)))
        (CONNECT_DATA =
                (SERVER = DEDICATED)
                (SERVICE_NAME = *service*)))

Finally, I added environment variables, and restarted the application. I tried with the original simpler URL, and it was clear that the settings weren't picking up, as I would expect with the trace flags that a file would be created if it were being read. Next I tried to create the URL using the TNS name, which per the docs sounded like it would look in TNS_ADMIN for additional files to connect with.

jdbc:oracle:oci:@(DESCRIPTION=(ADDRESS LIST=(ADDRESS = (PROTOCOL = TCP)(HOST = *host*)(Port = *port*)))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = *service*)))

So far, I haven't seen any trace files come up, and I have a feeling I'm doing a part of this wrong, or missing a step, or using the wrong URL. I tried a few variants of connection strings that would reference "APPDB" as in my tnsnames.ora file, but either it doesn't work at all, or it connects, but does not seem to be picking up the settings from sqlnet.ora.

Any ideas on what I could be missing, or if in fact applying these settings is only possible on the DB side?

The difference of oracle instant client and the oracle client installed by Oracle Universal Installer is in how they are installed. The Oracle universal installer maintains a registry which the instant client does not have or use. But the software components are the same independent of the method you use to install. If you use the jdbc oci driver then you use java to access the libraries of the Oracle OCI client which is the well known client that use all the clients that can use a tnsname.ora. These are sqlplus, ODBC program, C-Programs that use the Oracle Precompiler, ... Access with the OCI client can be configured with tnsnames.ora and sqlnet.ora and the well known environment variables (like ORACLE_HOME, ORACLE_SID, TNS_ADMIN,...). In java there is a new way to access the Oracle database by using the jdbc thin client. This is a client completely implemented in java that does not use the oracle OCI libraries but has implemented the necessary oracle net protocols in java to communicate with an Oracle database. This client cannot be influenced by tnsnames.ora, sqlnet.ora or the environment variables. It does not even need these files. It only needs an appropriate jar file that implements the thin client.

If you use the oci client for a program then  in the environment that starts this program the Oracle relevaant variables must be set. So if a program is started by a shell script this shell script should set and export the relevant variables. The directory stored in the  variable TNS_ADMIN is searchd for the tnsnames.ora and sqlnet.ora. If this variable is not set then other directories are searched for these files (or similar files). This depends on the operating system and of the value of ORACLE_HOME, if set. If no appropriate file is found default values are used.

DIAG_ADR_ENABLED=OFF determines if ADR-logging (available since 11.1)  is used or the traditional logging is used. If ADR-logging is not disabled the TRACE_DIRECTORY_CLIENT parameter will be ignored and the sqlnet trace files will be found somewhere in the diag-directories used by ADR-logging.


Is there a way to test whether DELETE will fail due to constraints? I'd like to be able to predict whether a DELETE will run into a constraint violation, without actually performing the delete.

What are my options for doing this?  Is there a simple way to do a "dry run" of a DELETE?

If your goal is to process all deletes only if they all succeed, why not just use TRY/CATCH:

BEGIN TRANSACTION;
BEGIN TRY
  DELETE #1;
  DELETE #2;
  DELETE #3;
  COMMIT TRANSACTION;
END TRY
BEGIN CATCH
  ROLLBACK TRANSACTION;
END CATCH

If the goal is to allow all successful deletes to succeed even if one or more will fail, then you can use individual TRY/CATCH, e.g.

BEGIN TRY
  DELETE #1;
END TRY
BEGIN CATCH
  PRINT 1;
END CATCH

BEGIN TRY
  DELETE #2;
END TRY
BEGIN CATCH
  PRINT 1;
END CATCH


MDX - Getting the last 6 month Dynamically I have a Dim table that called DimAccounts.
It is describing a User account and the dates that the account has been created.
for Example:

I would like to get the list of accounts that have been created on the last 6 month Dynamically. For example today is the 09\01\2016.
So my list of accounts will be the accounts created from the 01-08-2015 until 09-01-2016. Notice that this CreatedOn field does not have a hierarchy and it is an attribute of the accounts dimension .

Assuming you have properly set the ValueColumn property of your CreatedOn attribute in your dimension (so that MemberValue comes back a date data type) then the following should work:

select {[Measures].[Your Measure Here]} on 0,
[DimAccounts].[AccountPK].[AccountPK].Members on 1
from [Your Cube]
where {
 Filter(
  [DimAccounts].[CreatedOn].[CreatedOn].Members
  ,[DimAccounts].[CreatedOn].CurrentMember.MemberValue > DateAdd("m",-6,Now())
 )
}

Or if you want to create a named set so that any user can just drop it on rows, put this in the calculations tab of your cube designer:

CREATE SET CurrentCube.[Accounts Created In Last 6 Months]
as
Exists(
 [DimAccounts].[AccountPK].[AccountPK].Members,
 Filter(
  [DimAccounts].[CreatedOn].[CreatedOn].Members
  ,[DimAccounts].[CreatedOn].CurrentMember.MemberValue > DateAdd("m",-6,Now())
 )
);


Large table delete in mysql I want to delete a table with 10,000,000 records. Due to some restrictions I can't do truncate on that table (I don't have the DROP privilege), I can think of two options:

DELETE QUICK
To set some table level logging off so that mysql doesn't put data
in rollback segments and that could make it faster as it happens
in oracle.

Please throw some light on it while keeping in mind that I can't use TRUNCATE.

If you don't have privileges to DROP table you should DELETE in chunks.

Use MySQL Limit clause to delete in chunks.

DELETE FROM TABLE_NAME LIMIT 10000;


Is it advisable to asynchronously update the summary table every time a transaction is deleted? Given three tables:

(a) Product
(b) Transaction Head
(c) Transaction Detail

We need to create a query that fetches a list of products with associated latest ordered quantity and latest transaction date.

We also have a web page where the transaction can be created, edited and deleted.

The problem lies in the query performance. The data in the transaction head and detail tables may increase dramatically fast. Hence, executing a query with a subquery which finds the most recent transaction can be heavy.

We came up with a solution to create a summary table where it may contain the following columns:
(a) branch id
(b) product id
(c) latest quantity ordered
(d) latest ordered date

which will be updated in the transaction module. However, when a transaction entry is deleted, we had to run the whole query, order by date descending, and update the summary table since the previous state is not stored.

Is it advisable to asynchronously update the summary table using the heavy query every time a transaction is deleted? Would not it be too heavy for the server? We are also considering using cron - a job scheduler to synchronously re-compute the summary table for efficiency, but we had to display the information at real-time.

1M/year is 2 per minute.  You can do a lot of updating in 30 seconds.  1M/day (12/second) would be more exciting.

Do you need to rerun the summary queries?  Can't you just do a subtract via an UPDATE?  Possibly fast enough to be synchronous.

How often do you need to post a correction?  If it is rare, consider adding a row to the summary table with a negative number.  Then, when you do your reports, the SUM() comes out right.  (You do sums, correct?)

It seems just wrong to have summary tables but to continually rebuild the table.


Why doesn't the following stored procedure compile with MySQL Workbench version 6.3.? I would like to know how to make the following stored procedure compile with MySQL Workbench version 6.3.

use puppy;
DELIMITER $$
CREATE PROCEDURE spGetSystemLicensing
(
    IN  _licenseName varchar(128)
)
BEGIN

IF (TRIM(_licenseName) = '')
    select * from SystemLicensing;
ELSE
    select * from SystemLicensing where LicenseName=_licenseName;
END IF;

END

Here is my MySQL Workbench error message:

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select * from SystemLicensing' at line 8

Any help is greatly appreciated.

You're just missing the keyword THEN. Read more about the correct syntax here.

Try it like this:

use puppy;
DELIMITER $$
CREATE PROCEDURE spGetSystemLicensing
(
    IN  _licenseName varchar(128)
)
BEGIN

IF (TRIM(_licenseName) = '') THEN
    select * from SystemLicensing;
ELSE
    select * from SystemLicensing where LicenseName=_licenseName;
END IF;

END $$
DELIMITER ;


Why --audit commands does not work in Mongo Enterprise I am trying to make destination for my user activity log but every time I type --auditDestination I get errormessage "unrecognized option --auditDestination", can anyone tell me what should I do?

I really need a help with this, if anyone can help with some solution I will be grateful :/

--auditDestination is parameter of mongoD and mongoS, if you try to use it with mongo command, you get error message.


Advantages of Database Snapshot Can someone please highlight the advantages of Database Snapshot in SQL2008.

I read some where that

"When a Database Snapshot is created, SQL Server doesn’t allocate space on disk equivalent
to the current size of the data files in the source database. Instead, SQL Server takes advantage of an operating system feature called sparse files. A sparse file is essentially an entry in the file allocation table and consumes almost no space on disk."

Can someone please also tell me how can a file created with no space allocated.

Thanks

A snapshot is great for these scenarios where you might have used a database backup in the past:

before doing database schema upgrades
before running adhoc data manipulation queries
maintaining a historical snapshot you might want to analyze later
materializing data on standby servers

In some of the above scenarios, a transaction is a better option to automatically roll back from problems, but there are sometimes cases where the query might succeed but have unintended/unforeseen consequences, so a snapshot is a good standby option to revert a bad SQL script.  Of course, if this is a live system with commits occurring actively, then you wouldn't be able to go back to the snapshot without losing any commits that occurred since the snapshot was made(another reason transactions are better protection when running sql scripts against a live system).

Since the snapshot occupies less disk space, and are faster to create than a backup, they are a better option than backups in these cases.

You would still use traditional database backups to protect against disk array failures, malware, natural disasters, etc.

http://www.simple-talk.com/sql/database-administration/sql-server-2005-snapshots/


How to grant execute permissions to a stored procedure but not to the underlying databases I am trying to allow a user to run a stored procedure while restricting them read/write access to the underlying tables. I am able to complete this with other stored procedures, but the one I am working with contains tables in a second database.

I have restricted the user from accessing the underlying tables in the database the stored procedure was created in (database ORMAN), but the only way this stored procedure will run is if I give the user READ permissions to the entire second database that contains one of the tables called in the stored procedure (database PSMAN).

This is the setup:

Databases:

ORMAN
PSMAN

User:

BI_User
ORMAN database access, has access to execute stored procedure (BI_Datadownload).
PSMAN database access, assigned db_datareader access.

Stored Procedure (BI_Datadownload):

Found in database ORMAN.
Calls table in another database PSMAN

Any help would be appreciated.

Cheers.

This is easy to accomplish with Module Signing.

The concept is to create a certificate in the database that the user, BI_User, has access to (and contains the stored procedure), [ORMAN], which will then be used to sign the stored procedure, BI_DataDownload,  that has the functionality the are currently restricted from accessing.

The certificate is then copied (public key only) into the restricted database, [PSMAN], for the purpose of creating a local user from it. That local certificate-based user is then assigned whatever permission(s) are needed in order for the BI_DataDownload stored procedure to complete successfully.

The BI_User account still only has permission to connect to [ORMAN] and to execute BI_DataDownload. No permissions are being granted to that account (Login or User) at all. The permissions are granted only to the certificate-based user that exists in the [PSMAN] database. The connection between the BI_DataDownload stored procedure and the certificate-based user is (effectively) the public key of the certificate (which is why we only needed to copy that part to the [PSMAN] database). And that public key is only loaded into the session's security context when a module that has been signed with that certificate is being executed. And that signature requires the certificate's password (in the ADD SIGNATURE statement), and is dropped if anyone changes even a single byte of the stored procedure (or the owner of it). Hence, very secure.

The following illustrates this concept:

Main Setup

USE [master];

CREATE DATABASE [ORMAN];
ALTER DATABASE [ORMAN] SET RECOVERY SIMPLE;

CREATE DATABASE [PSMAN];
ALTER DATABASE [PSMAN] SET RECOVERY SIMPLE;

CREATE LOGIN [BI_User] WITH PASSWORD = 'DBA.SE_239700';
GO

USE [PSMAN];
CREATE TABLE dbo.RemoteTable
(
  [RemoteTableID] INT NOT NULL IDENTITY(1, 1)
    CONSTRAINT [PK_RemoteTable] PRIMARY KEY,
  [SomeValue] NVARCHAR(50) NULL
);

INSERT INTO dbo.[RemoteTable] ([SomeValue]) VALUES (N'one');
INSERT INTO dbo.[RemoteTable] ([SomeValue]) VALUES (N'two');
GO

USE [ORMAN];
CREATE USER [BI_User] FOR LOGIN [BI_User];

GO
CREATE PROCEDURE dbo.[BI_DataDownload]
AS
SET NOCOUNT ON;

SELECT rt.[RemoteTableID], rt.[SomeValue]
FROM   [PSMAN].[dbo].[RemoteTable] rt;

GO

GRANT EXECUTE ON dbo.[BI_DataDownload] TO [BI_User];
GO

Test Current Situation (no extra permissions)

EXECUTE AS LOGIN = N'BI_User';
SELECT SUSER_NAME() AS [Login], USER_NAME() AS [User];

EXEC dbo.[BI_DataDownload];
/*
Msg 916, Level 14, State 1, Procedure dbo.BI_DataDownload,
            Line XXXXX [Batch Start Line YYYYY]
The server principal "BI_User" is not able to access the database
       "PSMAN" under the current security context.
*/

REVERT;
SELECT SUSER_NAME() AS [Login], USER_NAME() AS [User];

Module Signing Setup

-- Create Certificate and sign Stored Procedure with it:
CREATE CERTIFICATE [Permission$PSMAN]
    ENCRYPTION BY PASSWORD = 'SomePassword'
    WITH SUBJECT = 'Used for accessing PSMAN',
    EXPIRY_DATE = '2099-12-31';

ADD SIGNATURE
    TO dbo.[BI_DataDownload]
    BY CERTIFICATE [Permission$PSMAN]
    WITH PASSWORD = 'SomePassword';

---
-- Copy Certificate to [PSMAN] DB:
DECLARE @CertificatePublicKey NVARCHAR(MAX) =
            CONVERT(NVARCHAR(MAX), CERTENCODED(CERT_ID(N'Permission$PSMAN')), 1);

SELECT @CertificatePublicKey AS [Cert / PublicKey]; -- debug

EXEC (N'
USE [PSMAN];
CREATE CERTIFICATE [Permission$PSMAN] FROM BINARY = ' + @CertificatePublicKey + N';');
---

-- Create User in [PSMAN] DB to allow for extending permssions there:
EXEC (N'
USE [PSMAN];
CREATE USER [Permission$PSMAN] FROM CERTIFICATE [Permission$PSMAN];

GRANT SELECT ON dbo.[RemoteTable] TO [Permission$PSMAN];
');

GO

Test with Module Signing

EXECUTE AS LOGIN = N'BI_User';
SELECT SUSER_NAME() AS [Login], USER_NAME() AS [User];

EXEC dbo.[BI_DataDownload];  -- SUCCESS!!!!!!!!
/*
RemoteTableID    SomeValue
1                one
2                two
*/

REVERT;
SELECT SUSER_NAME() AS [Login], USER_NAME() AS [User];


How can I make an Aging Report using mysql? I have a table called supplier

CREATE TABLE supplier_invoices (
id INT PRIMARY KEY AUTO_INCREMENT,
supplier_id INT NOT NULL,
date DATE NOT NULL,
invoice_no INT NOT NULL,
dr_amount DOUBLE NOT NULL,
cr_amount DOUBLE NOT NULL,
balance DOUBLE NOT NULL);

and it contains the above fields. I want to create an aging report basing on the data in that table. With difference of days between 0-30days, 31-60days, 61-90days, 90 and above days.

How can I achieve this. Am using mysql.

The CASE statement will work. It returns a value depending on a condition, so you can assign each invoice's value to exactly one age bucket.

To be able to roll up the values at whatever granularity required use a sub-query to put values into appropriate buckets, then in the super-query perform the required grouping.

select
  sum(Balance30) as Balance30,
  sum(Balance60) as Balance60,
  ..etc.
from (
  select
    supplier_id,
    case
      when datediff (curdate (), order.date) <= 30 then order.balance
      else 0
    end as Balance30,
    case
      When datediff (curdate (), order.date) > 30
        and datediff (curdate (), order.date) <= 60 then order.balance
      Else 0
    End as Balance60,
  ... etc
) as SomeAlias
group by
  supplier_id,
  .. or whatever

DATEDIFF calculates how old each invoice is, according to your chosen reference date. The CASE decides which bucket it should be in, and returns zero for each row which is not in this bucket. This assigns each invoice's value into exactly one of the buckets. The output from the inner query is one row per row in the base table, with key values and one column per bucket. The SUM in the outer query tallies all rows according to the grouping conditions, but we've substituted zero for non-matching rows so effectively filtered them out.

This assumes you want a column per bucket. If you need a row per bucket the same technique applies. Instead of returning order.balance from the CASE, return a bucket name. Wrap the whole query in group by bucketName and you will have the desired outcome.


Add SQL Server Agent to existing SQL Installation If there is a SQL server installation that is physically missing the SQL Server Agent is there a way to add it without reinstalling SQL Server? Will installing a service pack do it?

Today I was asked to look at a SQL server another department had installed. It's SQL 2016 SP1 Standard Edition. SSMS shows SQL Agent, but there is no SQL Agent service in services or in Configuration Manager. And no mention of SQL Agent in the registry.

In the installation path I see the various SQL Agent files and I tried to register the service via the SC command but it didn't work. The service is there but it won't start.

Short of detaching the databases, uninstalling and reinstalling, is there anything else that can be done to get it working?

Try to run a repair intallation from the installation media and then install service pack and cu after that.

You can run the repair from the command prompt:

Setup.exe /q /ACTION=Repair /INSTANCENAME=instancename

Or by opening the installation center and select Maintenance in the left-hand navigation area, and then click Repair to start the repair.

Step-by-step instructions are found in the documentation


Migrating 300GB database to Azure SQL Server We are having a devil of a time trying to migrate our current on-premises SQL Server to an Azure SQL Database. It is large but not that large (about 300 GB). We've tried the "Publish to Azure" option from SQL Server and get errors and exceptions (out of memory exceptions). The Data Migration Assistant seems to start working OK, but it fails after 12 hours or so having completed only about 15% of the migration. We can't be down for 12 hours, let alone the time it would seem to take in order to move the complete 300 GB database.

What are some strategies and best practices that we could try for the data migration that would minimize downtime?

Have you tried the Azure Data Migration Service located here? You can try the service for free from now until August 31, 2018. For more information, please click here.


Combine results of Multi-Server Query from Registered Servers Is there a way to insert result of a query executed in Registered Servers into a view / sp / table?

Currently I have locally setup a Registered Server on multiple servers to query the master database for each.

I basically want to insert the result in a single container then read it by a job.

It this possible?

No, there is no built-in way to accomplish this due to each query in a Multi-Server query being executed in a separate connection to each individual server. The results only exist together within SSMS. I always had to copy and paste the results grid into Excel so that I could do further aggregations and sorting.

Fortunately, I am very close to releasing a new product that does exactly what you are wanting to do. There will be a free version that has some limitations. A paid version allows for multithreading. It currently comes in two forms: command-line utility and SQLCLR Stored Procedure.

If you are interested in beta-testing, please contact me. I will update this answer with more details once it is officially released.


Why is TNSLSNR loading wrong listener? I'm using ORACLE 12c. I have 1 CDB and 1 PDB (for what I know).
To practice, I have created 2 listeners. I have made the appropriate settings in my tnsnames.ora file.

Observations:

when I just run lsnrctl start i.e. without specifying one on my listeners name, **TNSLSNR actually loads a listener located in /ora01/app/oracle/product/12.1.0/db_1/bin/tnslsnr (this is the path indicated by ps)
Before running lsnrctl start, there is no listener.ora in $ORACLE_HOME/network/admin. I have renamed it to xxxxxlistener.ora for testing what "zombie" listener was loaded by simple lsnrctl start. The worst part is that the response message says it loaded a listener from $ORACLE_HOME/network/admin/listener.ora. This is AWFUL since I renamed that file to xxxxxlistener.ora

There is a ghost file on my computer. Please help me as I can't even practice my custom listeners.

I can't practice my custom listener as if I don't put them on port 1521, I can NEVER user them to connect on the DB. That "ghost" listener seems to be messing things up

lsnrctl START [listener_name]

  In the preceding command, listener_name is the name of the listener defined in the listener.ora file. It is not necessary to identify the listener if you are using the default listener name LISTENER.

If you just hit lsnrctl start without the listener name then Oracle tries to start default listener which listens on two address:

(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY))
(ADDRESS=(PROTOCOL=tcp)(PORT=1521))

If you wish to start a specific listener then, first of all define the listener in listener.ora file and specify listener name to start it as lsnrctl start listener_name as shown below:

[oracle@ora12c admin]$ lsnrctl start listener3

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 07-JAN-2017 19:53:16

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/12.1.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/ora12c/listener3/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.30.133)(PORT=1523)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.30.133)(PORT=1523)))
STATUS of the LISTENER
------------------------
Alias                     listener3
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                07-JAN-2017 19:53:17
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/ora12c/listener3/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.30.133)(PORT=1523)))
The listener supports no services
The command completed successfully
[oracle@ora12c admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/grid/product/12.1.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER2 = ON

VALID_NODE_CHECKING_REGISTRATION_LISTENER = SUBNET

VALID_NODE_CHECKING_REGISTRATION_LISTENER2 = SUBNET

LISTENER1 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.30.133)(PORT = 1522))
    )
  )

LISTENER3 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.30.133)(PORT = 1523))
    )
  )

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON

Set the local_listener initialization parameter. Its up to you use the number of listeners. In my case I have used both.

SQL>alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.30.133)(PORT=1522))','(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.30.133)(PORT=1523))';

Update

  ORA-00119: invalid specification for system parameter LOCAL_LISTENER

  ORA-00132: syntax error or unresolved network name 'LISTENER1'

Oracle says-

  00132, 00000, "syntax error or unresolved network name '%s'"

  Cause:  Listener address has syntax error or cannot be resolved.

  Action: If a network name is specified, check that it corresponds
            to an entry in TNSNAMES.ORA or other address repository
            as configured for your system. Make sure that the entry
            is syntactically correct.

When we create a database and provide the listener name, Oracle creates a TNS alias in tnsnames.ora file and that alias is used as local_listener value.

$ cat $ORACLE_HOME/network/admin/tnsnames.ora
#LISTENER1 =
#  (ADDRESS = (PROTOCOL = TCP)(HOST = ora12c.dba.com)(PORT = 1522))

In your case the alias name should be LISTENER_ORA12C instead of LISTENER1.
I had commented that alias to simulate the scenario.

So go to the tnsnames.ora make sure that alias is there. Then try again to start your Oracle instance.

[oracle@ora12c admin]$ vi tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER1 =
  (ADDRESS = (PROTOCOL = TCP)(HOST = ora12c.dba.com)(PORT = 1522))

[oracle@ora12c admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun Jan 8 09:10:35 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  838860800 bytes
Fixed Size          2929936 bytes
Variable Size         624954096 bytes
Database Buffers      100663296 bytes
Redo Buffers            5455872 bytes
In-Memory Area        104857600 bytes
Database mounted.
Database opened.
SQL> show parameter local_listener

NAME                     TYPE    VALUE
------------------------------------ ----------- ------------------------------
local_listener               string  LISTENER1

Reference:Configuring and Administering Oracle Net Listener


Copy Huge Table with Transformations into Another Table I have a large table that was imported from a CSV using the import wizard.  I have a query that selects from this table and replaces all of the string "code" columns with integer ids from other dimension tables and performs some transformations on some other columns.  This all works well.

I need to insert the results of this query into a table in the database that has the required foreign key links and indices.  This is also the table that the SQL objects (sprocs, functions, views) use to access the data.

My issue is that the insert takes forever (1.2 hrs) before it eventually dies by complaining about the size of the transaction log.  I don't have access to log onto the server.  I only have access through SSMS with db_owner rights on the database in question.

My questions are:

How can I perform the insert without logging it in the transaction log?  I know there is BULK INSERT and OPENROWSET(BULK ...) but these require a file.
Would the insert go faster if I drop the indices? I think this would break the execution plan of the existing SQL objects that use the destination table. Is this advisable if there is a clustered index on the destination table?
Is not logging the insert the correct way to go or should I use some other method?

If this is a one-time job, you might just use the BCP Utility to do this task.

It seems that your hanging point is doing a massive import that exceeds the resources of your disk space.  The thing you need to do is to break the import into batches, which BCP supports, so as to avoid a transaction that cannot fit into your server.

When I have had to make a single massive data transfer, I have had good success using the BCP utility to extract the data to a file.  This does, of course, require a file location to temporarily hold the extracted data.

Use The BCP Utility examples from MSDN as samples. (Scroll to see entire commands.)

Copy Data to File Storage:

 bcp WorlWideImporters.Warehouse.StockItemTransactions OUT D:\BCP\StockItemTransactions_native.bcp -m 1 -n -e D:\BCP\Error_out.log -o D:\BCP\Output_out.log -S ServerA -T

To import the data back into a database using BCP you should define a batch size, such as -b 5000 (commit transactions ever 5000 rows) so as to avoid filling up the transaction log.

You may also need to schedule the log backups more frequently during the massive import that you describe. Or you can switch to SIMPLE mode during the insert to the database.

Copy from File Storage to Database

bcp WorlWideImporters.Warehouse.StockItemTransactions_bcp IN D:\BCP\StockItemTransactions_native.bcp -b 5000 -h "TABLOCK" -m 1 -n -e D:\BCP\Error_in.log -o D:\BCP\Output_in.log -S ServerA -T

These are reliable tools, but not the fanciest. For importing data using BULK INSERT which is, in my experience, runs faster that BCP and it also has a parameter for controlling batch size.

You should reference The BCP Utility and BULK INSERT if your needs are more complex than the simple examples I have used.

However, please notice this post with several answers: Freeing Unused Space SQL Server Table

Note that Kin included the options mentioned is BCP and BULK INSERT, but there are other good options is the posts noted above.


ola.hallengren - Alternative MySQL scripts! Having worked with MS SQL for many years and found ola.hallengren scripts incredibly useful - I find my self in a MySQL environment.
Is there a MySQL alternative to ola.hallengren? - Perhaps someone has created a batch of scripts to show worst performing queries, re-indexing jobs etc that is popular in the area of Database optimisation, maintenance etc.
I've reviewed Optimize a MySQL Database using MySQL Workbench - Although useful, doesn't quite give me the flexibility of what I need - that Ola answers with there scripts.

You might like to visit https://github.com/shlomi-noach/awesome-mysql which is a list of recommended community tools for MySQL.
Specifically the kind of tools you want may be:

common_schema - DBA's framework for MySQL, providing a function library, views library and QueryScript interpreter.
sys - A collection of views, functions and procedures to help MySQL administrators get insight in to MySQL Database usage.
Percona Toolkit - a collection of advanced command-line tools to perform a variety of MySQL server and system tasks that are too difficult or complex to perform manually.
openark kit - a set of utilities that solve everyday maintenance tasks, which may be complicated or time consuming to do by hand, written in Python.


Is it possible to delete cache in Mongo? My case is that I run several queries in Mongo and measure the response time. I know that Mongo uses Memory Mapped files and cache some data in RAM.

If I create an index and run a query the execution time is 2,07 seconds. When I run the qeury again the execution time is 0,017 seconds.

This means that the file is mapped into memory by the OS on the first access and the OS will keep it in cache until it decides to page it out.

Because I want to make some benchmarks I want somehow to remove the mapped results from memory.

free -g:

               total       used       free     shared    buffers     cached

Mem:            29          29          0          0          0         29
-/+ buffers/cache:          0         29
Swap:            0          0          0

I try db.collection.getPlanCache().clear() but it doesnt work.

Then I try:

sudo service mongod stop

I reboot my machine

sync; echo 3 > /proc/sys/vm/drop_caches
sudo service mongod start

free -g:

               total       used       free     shared    buffers     cached

Mem:            29          0          0          0          0         0
-/+ buffers/cache:          0         29
Swap:            0          0          0

In above it seems that cache is free now. But when I execute again the query the execution time is 0,017 seconds. It seems that mongo has manage to cache or preserve the data somewhere. The desired execution time would be 2,07 seconds as I clear the cache.

Can anyone tell me how I can actually delete the cached results in Mongo?

As per MongoDB documentation here PlanCache.clear() removes all cached query plans for a collection.

The method is only available from the plan cache object of a specific collection; i.e.

db.collection.getPlanCache().clear()

Required Access

On systems running with authorization, a user must have access that includes the planCacheWrite action.

For your further refernce Manage Users and Roles in MongoDB and Collection-Level Access Control in MongoDB


Is accelerated database recovery available in SQL Server 2019? SQL Server 2019 CTP introduced a new feature – accelerated database recovery. But then something strange started to happen:

If we look at doc page about this feature, we would see no mention of
SQL Server 2019 – there’s Azure DB only.
If we look at sys.databases
description, we would see no mention of
is_accelerated_database_recovery_on column, while in fact, it exists
Same thing about accelerated_database_recovery database option.
However, feature matrix shows ADR available for Standard and Enterprise SQL Server editions.

On the one hand, enabling ADR could benefit with faster transaction rollback and lesser startup time.

On the other hand, feature seems undocumented. Therefore, it’s behavior is unpredictable. If something goes wrong with transaction log, I could literally lose some data.

So, does anybody knows: can I rely on ADR using SQL Server 2019? Is it postponed due to some instability or it is just a glitch in documentation?

The documentation page link in your question is specific to Azure SQL Database. Note Azure in the url:

https://docs.microsoft.com/en-US/azure/sql-database/sql-database-accelerated-database-recovery.

The Accelerated database recovery page in the general documentation shows ADR is included in SQL 2019 and Azure SQL Database.


Elastic queries across subscriptions in Azure SQL Is it possible to run cross-database, actually cross-subscription, in Azure SQL?

I have three subscriptions (Dev, Test and Prod) and I need to need to keep Test up to date with Prod.

Unless I'm not reading the many sites correctly they seem to all assume that the databases are in the same subscription albeit possibly different servers.

Yes Elastic queries across subscription is possible.

There are several limitations to this feature. For example:

  You must possess ALTER ANY EXTERNAL DATA SOURCE permission. This
  permission is included with the ALTER DATABASE permission. ALTER ANY
  EXTERNAL DATA SOURCE permissions are needed to refer to the underlying
  data source.

  Authentication using Azure Active Directory with elastic queries is
  not currently supported.

Read this for details: Azure SQL Database elastic query overview (preview)

Create 2 resource groups in two different subscriptions.
Create one Azure SQL Server and one empty Azure SQL database in each resource group.
Then you can follow along with the following code.

I used the following names in my setup:

# The logical server name: Use a random value or replace with your own value (do not capitalize)
$SqlServerName1 = "taiobeqdemo1"
$SqlServerName2 = "taiobeqdemo2"

# The database name
$customerDatabase = "taiobCustomer"
$orderDatabase = "taiobOrders"

--Connect taiobeqdemo1.database.windows.net
--Change database context to taiobCustomer

SET NOCOUNT ON;
CREATE TABLE [dbo].[CustomerInformation](
    [CustomerID] [int] NOT NULL,
  [CustomerName] [varchar](50) NULL,
  [Company] [varchar](50) NULL
CONSTRAINT [CustID] PRIMARY KEY CLUSTERED ([CustomerID] ASC)
)
GO
INSERT INTO [dbo].[CustomerInformation] ([CustomerID], [CustomerName], [Company]) VALUES (1, 'Jack', 'ABC');
INSERT INTO [dbo].[CustomerInformation] ([CustomerID], [CustomerName], [Company]) VALUES (2, 'Steve', 'XYZ');
INSERT INTO [dbo].[CustomerInformation] ([CustomerID], [CustomerName], [Company]) VALUES (3, 'Lylla', 'MNO');
GO

--Connect taiobeqdemo2.database.windows.net
--Change database context to taiobOrders

CREATE TABLE [dbo].[OrderInformation](
  [OrderID] [int] NOT NULL,
  [CustomerID] [int] NOT NULL
  )
GO
INSERT INTO [dbo].[OrderInformation] ([OrderID], [CustomerID]) VALUES (123, 1);
INSERT INTO [dbo].[OrderInformation] ([OrderID], [CustomerID]) VALUES (149, 2);
INSERT INTO [dbo].[OrderInformation] ([OrderID], [CustomerID]) VALUES (857, 2);
INSERT INTO [dbo].[OrderInformation] ([OrderID], [CustomerID]) VALUES (321, 1);
INSERT INTO [dbo].[OrderInformation] ([OrderID], [CustomerID]) VALUES (564, 8);
GO

--Connect taiobeqdemo1.database.windows.net

--Change database context to taiobCustomer

CREATE USER taiobelasticquery WITH PASSWORD = 'taiobelasticque$0';
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'taiobelastic$0';
GO

CREATE DATABASE SCOPED CREDENTIAL ElasticDBQueryCred
    WITH IDENTITY = 'taiobelasticquery',
    SECRET = 'taiobelasticque$0';
GO
--Create External data source
CREATE EXTERNAL DATA SOURCE MyElasticDBQueryDataSrc WITH
    (TYPE = RDBMS,
  LOCATION = 'taiobeqdemo2.database.windows.net',
  DATABASE_NAME = 'taiobOrders',
  CREDENTIAL = ElasticDBQueryCred,
);
GO

CREATE EXTERNAL TABLE [dbo].[OrderInformation]
( [OrderID] [int] NOT NULL,
  [CustomerID] [int] NOT NULL)
WITH
( DATA_SOURCE = MyElasticDBQueryDataSrc);
GO

--Connect taiobeqdemo2.database.windows.net
--Change database context to taiobOrders

CREATE USER taiobelasticquery WITH PASSWORD = 'taiobelasticque$0';
GO
EXEC sp_addrolemember 'db_datareader', 'taiobelasticquery';
GO

Now you are to do run elastic query from taiobeqdemo1.database.windows.net servers taiobOrders database.

--Change context to taiobOrders

SELECT
    OrderInformation.CustomerID,
    OrderInformation.OrderId,
    CustomerInformation.CustomerName,
    CustomerInformation.Company
FROM OrderInformation
INNER JOIN CustomerInformation
ON CustomerInformation.CustomerID = OrderInformation.CustomerID;
GO


Package parameter blank in SSIS catalog I have deployed a package with 2 package parameters which had their values set inside the package prior to deployment.  However if I look at those parameters in my SSIS catalog project the values of them are blank.

Is this usual behaviour?  Shouldn't the values have been passed to the parameters in my SSIS catalog?

Thanks

I would check what is setting the parameter value and make sure it is actually being set by debugging and setting a watch.  Also make sure that your parameter isn't being overwritten by a blank environment variable or something when it's deployed.


Move postgresql database from one server to another I have created a (spatial) database at my home which is several gigabytes and I want to move the whole database as it is from one computer my works computer.

Nothing fansy, I will create a new postgresql/postgis db instance at my work computer. The db users may or maynot have the same name in both computers.

Is it possible? What should i be carefull about? How can i do it pain free?

I'm using postgresql v9.1.5 with postgis extension. Ubuntu12.04 (kernel 3.2.0-31)

You can run a dump of the database:

pg_dump yourdatabase | gzip -9 > outfile.sql.gz

and then import back into PostgreSQL on your work computer. It will take quite some time and the resulting file, even if compressed, might be in the gigabyte range, but should be small enough to be contained on a USB key.

On your work computer, create the new database and load the dump:

zcat outfile.sql.gz | psql yournewdatabase

If you have a fast connection (or can take the time), you can even transfer the database through the Internet via an encrypted SSH connection, or tools such as rsync.


MySQL2::Error: BLOB/TEXT column can't have a default value I am trying to run a rails mysql migration on Amazon Elastic Beanstalk.

It fails with this error message:

  Mysql2::Error: BLOB/TEXT column 'description' can't have a default value: ALTER TABLE items ADD description text DEFAULT ''

I found out from this thread that Amazon RDS might be in strict mode. How do I change the mode?

BLOB, TEXT, GEOMETRY, and JSON columns cannot be assigned a default value.

  http://dev.mysql.com/doc/refman/5.7/en/data-type-defaults.html

What you are in essence asking -- without realizing it, presumably -- is how to force MySQL to revert to its old, incorrect behavior of ignoring your incorrect table definition and pretending that you really said ALTER TABLE items ADD description text NOT NULL but with the default value missing because that is the only alternative.  So you are left with a nonsensical table definition.

The new, correct behavior is indeed tied to the new recommended @@SQL_MODE defaults.  You're going to make trouble for yourself it your preferred solution is to unfix the server, rather than unbreak your table definition.

I understand that this is "working" for you now, but my point is that it is still in fact broken.  SHOW CREATE TABLE should reveal that your declared default value is in fact not there on your current production server.


MySQL Workbench Administrator - GUI "Access denied for user root " gotcha Here is a small newbie problem I found and resolved. The behavior of the GUI is a little confusing so I figure I will share.

Running some Java examples, I have MySQL set up on Windows. After making a little progress I ran into a problem with a sample database JDBC connection. I wanted to look at that database connection's admin user info in the Workbench to see what's going on.

So I click  "Users and Privileges" - the gui pops up a message:
"Access denied for user 'root'@'localhost' (using password: YES)"  - what? I know the root password, set it myself. There was no opportunity to enter it in the GUI just now, only a rude popup.

So I went hunting for some tip on how to update a config file for the GUI to read the root password.

Long story short - the previous night I ran the Java tutorial's SQL script to set the root password. It succeeded. However the GUI was not aware of this new password value, but still it was aware that there was a password, so I got the odd message.

If you see this message in a similar case, just quit the workbench and restart, enter the changed password and all is fine.


Improving a DbGeography query I am still new to database administration and I'm trying to optimise a search query.

I had a query which looked like this and took 5-15 seconds to execute in some cases, and also was causing 100% CPU usage:

DECLARE @point geography;
SET @point = geography::STPointFromText('POINT(3.3109015 6.648294)', 4326);

SELECT TOP (1)
     [Result].[PointId] AS [PointId],
     [Result].[PointName] AS [PointName],
     [Result].[LegendTypeId] AS [LegendTypeId],
     [Result].[GeoPoint] AS [GeoPoint]
FROM (
    SELECT
        [Extent1].[GeoPoint].STDistance(@point) AS distance,
        [Extent1].[PointId] AS [PointId],
        [Extent1].[PointName] AS [PointName],
        [Extent1].[LegendTypeId] AS [LegendTypeId],
        [Extent1].[GeoPoint] AS [GeoPoint]
    FROM [dbo].[GeographyPoint] AS [Extent1]
    WHERE 18 = [Extent1].[LegendTypeId]
)  AS [Result]
ORDER By [Result].distance ASC

This table has one clustered index on the PK, and one spatial Index on the geography type column.

So when I executed the above query, it was performing a scan operation.

So I created a non-clustered index on the LegendTypeId column:

CREATE NONCLUSTERED INDEX [GeographyPoint_LegendType_NonClustered] ON [dbo].[GeographyPoint]
(
    [LegendTypeId] ASC
)
INCLUDE (   [PointId],
    [PointName],
    [GeoPoint])
    WITH (PAD_INDEX = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    SORT_IN_TEMPDB = OFF,
    DROP_EXISTING = OFF,
    ONLINE = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

and changed the query to:

DECLARE @point geography;
SET @point = geography::STPointFromText('POINT({0} {1})', 4326);

 SELECT TOP (1)
     [GeoPoint].STDistance(@point) AS distance,
     [PointId],
     [PointName],
     [LegendTypeId],
     [GeoPoint]
     FROM [GeographyPoint]
 WHERE 18 = [LegendTypeId]
 ORDER By distance ASC

And now SQL Server performs a seek instead of the scan:

which in my view has increased the efficiency of the query, but when I deploy this to production, I still get the same results (high CPU usage, and average of 10 seconds to execute the query).

  Note: No data is inserted, updated or removed from this table – only
  search/read.

Is it something I am doing wrong?
How can I fix this?

EDIT

Index Seak details

EDIT 2:

I changed the query, to use the method: 'Nearest Neighbor' from link: https://msdn.microsoft.com/en-us/library/ff929109.aspx, and now this is the result, This Query is also taking 3-5 seconds for the search - similar to the Second Query, (but not tested on Production)

Spatial Index settings:

CREATE SPATIAL INDEX [SPATIAL_Point] ON [dbo].[GeographyPoint]
(
[GeoPoint]
)USING  GEOGRAPHY_GRID
WITH (GRIDS =(LEVEL_1 = MEDIUM,LEVEL_2 = MEDIUM,LEVEL_3 = MEDIUM,LEVEL_4 = MEDIUM),
CELLS_PER_OBJECT = 16, PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE =
OFF, SORT_IN_TEMPDB = OFF,
 DROP_EXISTING = OFF,
 ONLINE = OFF,
 ALLOW_ROW_LOCKS = ON,
 ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

EDIT 3
I followed instructions by @MickyT, dropped the Index on [LegendTypeId],and executed the following query:

DECLARE @point geography;
SET @point = geography::STPointFromText('POINT(3.3109 6.6482)', 4326);

SELECT TOP (1)

    [PointId],
    [PointName],
    [LegendTypeId],
    [GeoPoint]
FROM [GeographyPoint] WITH(INDEX(SPATIAL_Point))
WHERE
   [GeoPoint].STDistance(@point) IS NOT NULL AND
    18 = [LegendTypeId]
ORDER By [GeoPoint].STDistance(@point) ASC
OPTION(MAXDOP 1)

Statistics for this query are

And then I executed this Query again:

DECLARE @point geography;
SET @point = geography::STPointFromText('POINT(3.3109 6.6482)', 4326);

 SELECT TOP (1)
     [GeoPoint].STDistance(@point) AS distance,
     [PointId],
     [PointName],
     [LegendTypeId],
     [GeoPoint]
     FROM [GeographyPoint] --WITH(INDEX(SPATIAL_Point))
 WHERE 18 = [LegendTypeId]
 ORDER By distance ASC

Statistics for this query are

I used the following setup to run some tests against.

CREATE TABLE GeographyPoint (
    ID INTEGER IDENTITY(1,1) NOT NULL PRIMARY KEY,
    GeoPoint GEOGRAPHY NOT NULL,
    LegendTypeID INTEGER NOT NULL
    );

INSERT INTO GeographyPoint (GeoPoint, LegendTypeID)
SELECT TOP 1000000
    Geography::Point(RAND(CAST(NEWID() AS VARBINARY(MAX))) * 2,RAND(CAST(NEWID() AS VARBINARY(MAX))) * 2,4326),
    CAST(RAND(CAST(NEWID() AS VARBINARY(MAX))) * 25 AS INTEGER)
FROM Tally;

CREATE INDEX GP_IDX1 ON GeographyPoint(LegendTypeID) INCLUDE (ID, GeoPoint);
CREATE SPATIAL INDEX GP_SIDX ON GeographyPoint(GeoPoint) USING GEOGRAPHY_AUTO_GRID;

This gives a table of 1,000,000 random points with a 2 x 2 degree spread.
After trying a few different options on it, the best performance I could get was forcing it to use the spatial index.  There was a couple of ways to achieve this.  Dropping the index on LegendTypeID or using a hint.You will need to decide which is best for your situation.  Personally I don't like using index hints and would drop the other index if it is not required for other queries.

The queries stacked up against each other

DECLARE @point geography;
SET @point = geography::Point(1,1,4326);
/*
Clustered index scan (PK)
 SQL Server Execution Times:
   CPU time = 641 ms,  elapsed time = 809 ms
*/
SELECT TOP (1)
    [GeoPoint].STDistance(@point) AS distance,
    [ID],
    [LegendTypeId],
    [GeoPoint]
FROM [GeographyPoint]
WHERE 18 = [LegendTypeId]
ORDER By distance ASC
OPTION(MAXDOP 1)
/*
Index Seek NonClustered (GP_IDX1)
 SQL Server Execution Times:
   CPU time = 2250 ms,  elapsed time = 2806 ms
*/
SELECT TOP (1)
    [GeoPoint].STDistance(@point) AS distance,
    [ID],
    [LegendTypeId],
    [GeoPoint]
FROM [GeographyPoint]
WHERE [GeoPoint].STDistance(@point) IS NOT NULL AND
    18 = [LegendTypeId]
ORDER By [GeoPoint].STDistance(@point) ASC
OPTION(MAXDOP 1)

/*
For the next 2 queries
Clustered Index Seek (Spatial)
 SQL Server Execution Times:
   CPU time = 15 ms,  elapsed time = 11 ms
*/
SELECT TOP (1)
    [GeoPoint].STDistance(@point) AS distance,
    [ID],
    [LegendTypeId],
    [GeoPoint]
FROM [GeographyPoint] WITH(INDEX(GP_SIDX))
WHERE [GeoPoint].STDistance(@point) IS NOT NULL AND
    18 = [LegendTypeId]
ORDER By [GeoPoint].STDistance(@point) ASC
OPTION(MAXDOP 1)

DROP INDEX GP_IDX1 ON [GeographyPoint]

SELECT TOP (1)
    [GeoPoint].STDistance(@point) AS distance,
    [ID],
    [LegendTypeId],
    [GeoPoint]
FROM [GeographyPoint]
WHERE [GeoPoint].STDistance(@point) IS NOT NULL AND
    18 = [LegendTypeId]
ORDER By [GeoPoint].STDistance(@point) ASC
OPTION(MAXDOP 1)


MySQL won't start after upgrading to PHP 5.3 Just upgraded to PHP 5.3 with the latest version of mysql, but now mysql won't start. Below is mysqld.log

110516 22:24:46 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
110516 22:24:46 [ERROR] Error message file '/usr/share/mysql/english/errmsg.sys' had only 480 error messages, but it should contain at least 709 error messages. Check that the above file is the right version for this program!
110516 22:24:46 [Note] Plugin 'FEDERATED' is disabled.
/usr/libexec/mysqld: Unknown error 1146
110516 22:24:46 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
110516 22:24:46 InnoDB: The InnoDB memory heap is disabled
110516 22:24:46 InnoDB: Mutexes and rw_locks use GCC atomic builtins
110516 22:24:46 InnoDB: Compressed tables use zlib 1.2.3
110516 22:24:46 InnoDB: Using Linux native AIO
110516 22:24:46 InnoDB: Initializing buffer pool, size = 128.0M
110516 22:24:46 InnoDB: Completed initialization of buffer pool
110516 22:24:46 InnoDB: highest supported file format is Barracuda.
110516 22:24:46  InnoDB: Waiting for the background threads to start
110516 22:24:47 InnoDB: 1.1.6 started; log sequence number 10818302
110516 22:24:47 [ERROR] Aborting

110516 22:24:47  InnoDB: Starting shutdown...
110516 22:24:48  InnoDB: Shutdown completed; log sequence number 10818302
110516 22:24:48 [Note]
110516 22:24:48 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

Any clues?

Thanks. Help Appreciated!

I think the problem stems from the fact that the upgrade was done trying to use the mysql schema folder of the previous version of MySQL, since the message "Can't open the mysql.plugin table" appears.

MySQL 5.1/5.5 uses a table called mysql.plugin

Prior versions (MySQL 5.1/4.x/3.x) do not use mysql.plugin.

I also noticed another oddity. The error message also said the highest file format is Barracida. The innodb_file_format of earlier versions of mysql is Antelope. You may want to export the InnoDB data in the previous version of mysql you had running. Otherwise, you may not have proper access to the InnoDB files.

Here is what you should do:

Step 1) Make a backup copy of that mysql folder. (/var/lib/mysql/mysql and /var/lib/mysql)

Step 2) You should reinstall the MySQL 5.0 version that was there before.

Step 3) Once mysql 5.0 is back up, perform a mysqldump of all databases except the mysql schema to /root/OldData.sql. Make sure you get triggers, stored procedures and views as follows:

For example, if you have 5 databases, (information_schema, db1, db2, db3, and mysql), do not include information_schema and mysql. Just mysqldump all the others.

mysqldump -hhostaddr -umyuserid -pmypassword --routines --triggers --databases db1 db2 db3 > /root/OldData.sql

Step 4) Capture the mysql grants from MySQL 5.0 as follows:

mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A | sed 's/$/;/g' > /root/MySQLGrants.sql

Step 5) service mysql stop

Step 6) mv /etc/my.cnf /etc/my.cnf1

Step 7) Clear away /var/lib/mysql (Make sure you did Step 1)

Step 8) Uninstall mysql 5.0

Step 9) Install mysql 5.5 (MySQL 5.5 does not auto-install anymore)

Step 10) mv /etc/my.cnf1 /etc/my.cnf

Step 11) service mysql start

Step 12) Login to mysql and from the mysql client run the scripts

Step 13) Load the two SQL scripts

mysql> source /root/MySQLGrants.sql
mysql> source /root/OldData.sql

You should fine from here.

Give it a Try !!!


How can I maintain a "virtual column" which is composed of data from 2 tables? I have these two tables:

CREATE TABLE events (
    id integer NOT NULL,
    user_id integer NOT NULL,
    start_time time without time zone,
    end_time time without time zone
);

CREATE TABLE users (
    id integer NOT NULL,
    timezone_string character varying
);

The users change their timezone from time to time, but maintain the same schedule relative to the timezone where they are.

I want to be able to run this query:

select * from events where start_time >= '12:00' and end_time < '12:00';

where '12:00' is UTC.

But I want start_time and end_time to be adjust for each user's individual timezone. I can join with the users table and run a function to make the adjustment...

select * from events inner join users on (user_id = users.id)
where
  start_time::time at time zone 'UTC' at time zone timezone_string >= '12:00'
  and
  end_time::time at time zone 'UTC' at time zone timezone_string < '12:00';

...but that means each time I do this query I have to iterate over the entirety of both tables.

What are the ways I can precalculate the timezone adjustment, and auto-update the adjustment when data in either table changes? A materialized view needs to be completely rebuilt on each refresh, so that's out of the question. The best idea I have so far is to simply make a cache table that holds the adjusted data, and put triggers on the appropriate schedule and user columns to maintain the cache table.

You probably want to store event dates as TIMESTAMP WITH TIME ZONE because just the HH:mm:ss+TZ time without the corresponding date might be ambiguous or misleading -- for example, America/New_York timezone points to two different times during the summer and the winter due to daylight saving changes. Same goes for times that you query against (12:00):

CREATE TABLE users (
    id SERIAL PRIMARY KEY NOT NULL,
    name VARCHAR NOT NULL,
    timezone VARCHAR NOT NULL
);

CREATE TABLE events (
    id SERIAL PRIMARY KEY NOT NULL,
    user_id INT NOT NULL REFERENCES users (id) ON DELETE CASCADE,
    start_timestamp TIMESTAMP WITH TIME ZONE NOT NULL,
    end_timestamp TIMESTAMP WITH TIME ZONE NOT NULL
);

CREATE INDEX events_start_timestamp ON events (start_timestamp);
CREATE INDEX events_end_timestamp ON events (end_timestamp);

INSERT INTO users (name, timezone) VALUES
    ('Linas', 'GMT+2');
INSERT INTO events (user_id, start_timestamp, end_timestamp) VALUES
    (1, '2018-02-27 12:00:00-03', '2018-02-27 15:00:00-03');

With that in place, one can query against precalculated timestamps pretty easily (correct timezone conversion will be done behind the scenes by the database itself) and convert the resulting timestamp to user's timezone too:

SELECT users.id,
       users.name,
       (events.start_timestamp::TIMESTAMP AT TIME ZONE users.timezone) AS user_start_timestamp
FROM events
    INNER JOIN users ON events.user_id = users.id
WHERE start_timestamp >= '2018-02-27 11:00:00-03'::TIMESTAMP WITH TIME ZONE
  AND end_timestamp < '2018-02-27 16:00:00-03'::TIMESTAMP WITH TIME ZONE;

(SQL Fiddle)


Users database to store access levels, can I move the non-essential information to another table? I would like some advice on the best database structure for my system.

I have different kinds of users with different access levels, and different information of course. I created a users table with the following fields:

id
student_number
password
first_name
last_name
address
email
mothers_name
mothers_occupation
fathers_name
fathers_occupation
religion
civil_status

The problem is that some users just don't need all those fields. The only user type that needs all those are the students users. The student users have a different 'environment' / module that sends a request to an api, that then sends back a json response.

Other fields, for other type of users, are not applicable to them, for example, an admin, he/she mustn't have those other fields (I could use dummy data, but I am looking for a cleaner approach).

I was thinking I can just create tables like this:

Users table

id
username
password

UserInfo table:

user_id (FK)
first_name
last_name
...

What do you think? Any examples/erd of big users database?

Users.

id, student_number, password, first_name, last_name, address, email, mothers_name,
mothers_occupation, fathers_name, fathers_occupation, religion, civil_status

Roles
id, name

UserRoleMembership
user_id, role_id

If the problem is that you actually do want to validate some of those fields - but only for some users, I would use inheritance:

users
id, username, password

student_user
id,
user_id,
last_name required,
first_name required,
etc.

admin_user
id,
user_id,
admin_info_fields...


Trigger or Stored Procedure? Let's say I have two tables, foo and bar:

CREATE TABLE foo(
   foo_id    serial    NOT NULL,
   count     integer   NULL,
   PRIMARY KEY(foo_id)
   );

CREATE TABLE bar(
   bar_id    serial    NOT NULL,
   foo_id    SERIAL    NOT NULL REFERENCES foo(foo_id),
   PRIMARY KEY(bar_id)
   );

When something gets inserted into bar with a given foo_id, the corresponding record in foo should have its count incremented. How exactly do I go about doing this? I'm fairly new to databases in general.

For example:

INSERT INTO foo VALUES(1, 0);
INSERT INTO bar VALUES(1, 1);

At the second line, count should be incremented at the record for foo_id = 1.

I also need to the count decremented on a delete, but I'm sure that's pretty similar to incrementing on insert.

Follow your INSERT statement with:

UPDATE bar SET count = (count+1) WHERE foo_id=1

And on delete:

UPDATE bar SET count = (count-1) WHERE foo_id=1


Copy all agent jobs to separate files based on date and keep five copies Looking for a crazy script to run after database backup ...

Want to look at all agent jobs and if modified date is equal to today save it to a shared drive, if not skip. Also want to delete the oldest saved file if there are more than five.  Any help on any step would be greatly appreciated!

A couple of suggestions:

Don't ask us to write an entire system for you.  It's probably ok if you have a particular problem with how some script operates, as long as it's DBMS related.
Realize that all SQL Server Agent Jobs are stored in the msdb system database.  In order to "save" jobs you could simply take a backup of the msdb database daily.  You should do that anyway, since there is a lot of useful history, etc, stored in there.
If you really need to "script" out the jobs, they're stored in msdb in the dbo.sysjobs and dbo.sysjobsteps tables.


Change a specific line of a view with conditions I've recently started working at a company that maintains between four hundred and five hundred client databases, and am doing various maintenance tasks on code (I am junior level after all).  I've been tasked with identifying a particular bug in a group of views across the entire scope of the company, and changing each one. I was able to identify all of the culprits already via SQLSearch (a lifesaver), but am now tasked with modifying one particular line of code to a new standard across almost 1,200 views.

I know:

The wrong line of code (there are 3 variations, i know all three)
The correct line of code I need to replace the wrong lines with
Which views and which databases have the error.
How many views in each database are currently 'wrong'

How can I easily modify the views to correct the error without doing it by hand?

This is very easy to achieve with PowerShell (in which you can use Regular Expression to do your work)
But here for simplicity purpose, I will use a t-sql solution with a simplified example as shown below

-- assume you have a view called dbo.v and you want to replace one line in this view
use tempdb
go
drop view dbo.v
go
create view dbo.v
as
    select A = 'test 1'
    union all
    select A = 'test 2' -- we will change this to 'test 222'
    union all
    select A = 'test 3'
go
-- assume we will replace the line select A = 'test 2'
declare @sql varchar(max);
declare @original_string varchar(300) = 'select A = ''test 2''';
declare @replace_string varchar(300) = 'select A = ''test 222''';

select  @sql=definition from sys.sql_modules
where object_id = object_id('dbo.v')

set @sql = replace(@sql, @original_string, @replace_string);
set @sql = replace(@sql, 'create view', 'alter view');
--print @sql -- uncomment it if needed

exec (@sql)
go


Getting multiple queries with "show transaction isolation level" in pg_activity I am using PostgreSQL server for my production use.

When I fire a query like

select * from pg_stat_activity

on my server, I am getting 98% of queries like

SHOW TRANSACTION ISOLATION LEVEL

and my server accepts only 100 connections, so I am not able to proceed further.

Why is this happening? How can I block all these queries?

If they're there; they're there, but they didn't magically pop out of thin air. While I'd love for you to come back and tell us what they were in your case it's usually one of two things,

Connection pooling
ORM or SQL abstraction

Sometimes these guys connect and start empty transactions, or prep a transaction. It's always a result of a bad library. It's excusable if the library is new or new to the database. If the library is years old and doing this, I would suspect they don't have too many eyes on it and look at alternatives.
From the comments,

Postgres doesn't create connections by its own. It's your application that creates those connections and runs those queries. Possibly a connection pool or something similar. – a_horse_with_no_name Sep 10 '13 at 9:03
Are you running a Java application? Using HikariCP, or maybe another connection-pooling option? I'm using HikariCP and seeing a similar thing when I log into psql and do select query from pg_stat_activity;... I was also wondering why this is occurring; my guess is this is a mechanism by which HikariCP keeps connections "fresh". – Chris W. Jan 16 '16 at 19:22

While it's possible it's an attempt at keeping a connection "fresh" the kosher way to do that is something like this
result = PQexec(imp_dbh->conn, "/* DBD::Pg ping test v3.6.0 */");

You may want to file a bug report to use that method instead.


Archiving tables with Access frontend and linked SQL Server I'm working on a project where the user has an Access 2013 MDB database with forms, reports, and queries. We're converting it to use linked tables to SQL Server 2008 R2. All the linked tables seem to be working well.

The user needs the ability to archive some tables annually, and be able to read the data. These are not particularly large tables (the whole MDB is 27MB). Currently, the users make a copy of the MDB file every year and use that as their archive. They need the old data for comparison with the current information.

How do you archive linked tables via Access? It's not feasible to copy the entire SQL Server schema every year. Maybe if we just back up the tables and link again? What's the recommended approach to this? Thanks.

Try creating a separate historical file with nothing but the archived data table. In the example below you can see there is a linked table. I imagine your linked SQL table appears similar but pictured as a blue globe and is connected. Right click the linked table and select Convert to Local Table. This will sever the link and create a local table. Name it [tblYourTable_2016] for example.

OR

You could create a Make Table Query and recreate it that way as well. Either way I would keep the history files in a separate and secure file.

[]


How Should TCP/IP be configured to allow an external application access to SQL via the same port #? My SQL Server instance is hosted on an internal network server (10.x.x.x address). It's a named instance currently using dynamic ports. An external application at a third-party hosting location connects to the instance through use of the IP address and port #. After each maintenance window I have to confirm the dynamic port # in use is the same as before as the firewall rule is set to allow connections between application host and database host on that specific port #.

Is this the correct way to ensure the externally-hosted app can connect to the host without having to change port numbers in the connection string or should another port forwarding method be used (i.e., port 9999 on application host is mapped to port 1434 on SQL host)? What configuration manager changes in the Network Configuration section should be applied to allow the external application to use the same port # but internal applications to rely on the Browser server to make the correct mapping?

Not sure if I fully understand the question. But the internal applications should be fine about the port number unless there are also firewalls internally, in which case you just allow the port through there.

For the external access, I'd consider a static port so you can stop doing what you have to do. In an ideal world you'd only really expose your SQL Server to the web server itself externally, so if the firewall can just trust the webservers IP only that is a good thing to do only, instead of just being wide open for that port for any incoming traffic.

But you just ned to set an static port in the network config:

So instead of a 0 for dynamic ports, type a port number in for the TCP Port you wish to use.


Database suggestion for a social network/ knowledge base community? I am looking into various database types and DBMS's for a new project I am wanting to start in the summer.

I have built systems in MySQL and postgreSQL , now I am wanting to expand my knowledge and experience in Databases.

My project will be a type of social network / aggregate knowledge thing. (still havent developed a term to describe it yet).

I have been looking at:

Cassandra (use its own type of query language); It seems to be good for feature rich content and delivering high performance query execution. However I am not too keen on it because it requires a java environment to work on and I would prefer to have nothing to do with Oracle.
MongoDB (noSQL type of DBMS) ; great scalability however you lose all the capabilities already available on the proven SQL language like business information queries.

Requirements of the system:

Data Text, dates , times, xml, small ints, blob,
Structure/behavioir: normalised 3NF, non realtime, relational , scalable, robust
Environment: unix/linux , no JAVA!, preferably run on C

I was wondering if you could point me to any other Database systems that I should research into.

I have also had a look at Object Relational Databases , I quite like the idea of them working with PHP objects (PDO's) however their performance seems a bit poor.

Seeing as there will be DBA's here, any feedback on these systems that you have operated would be appreciated.

Thanks

Consider also that there is no reason why you can't use a relational database for some things and the nosql database for other things.


Software Engineers and DBAs What are main differences between Database Administrators and Software Engineers?  To what extent should a Software Engineer know the details of the underlying database?  Where is the border between these two professions?

It depends entirely on the breakdown of work at an organization, and what each person's responsible for.  But they're just labels; someone titled 'software engineer' at one company might be 'programmer/analyst' at another company or even 'systems programmer', etc.   There is no hard border between the two.

Where I work, the 'software engineer' tends to be the person doing both the design and the actual implementation, and we don't have anyone titled 'DBA';  The maintenance type tasks that would typically be a DBA fall back to the general 'system administrators', based on the guidance that the 'software engineer' gave them.

In what I personally think is a best case scenario, you'd break the design into multiple parts:

data modeling
requirements for the data storage (record structure, size of tables, number of inserts/deletes/updates per second, how quickly processes need to complete, etc)
requirements for disaster recovery / continuity of operations (needs to be back up within (x) hours, can't have longer than an (y) hour maintenance window each (month) )
implementation of the storage (selection of the database software, selection of the physical storage, how the tables are spread out on the storage, etc.)
implementation of the backup plan

And then the maintenance type tasks:

tuning the database
tuning the queries
debugging when things go wrong
overseeing & verifying the backups
applying software updates

For most of these, they don't have to be done by a DBA; it could be done by a software engineer, or in the case of some of the maintenance tasks, a system administrator.

If you have people in both roles, you might have them confer and collaborate on the design and tuning (what they'd call in construction 'design-build') , or if it's a rush job, you might assign the various tasks between the two.  You might have other people involved, too : a 'software architect', 'data architect', an archivist, various programmers, system administrators, network administrators, security, etc.


Database layout to keep track of the "freshness" of a column value in a specific row I have a Person with columns like name, address and bio.
How do I store data in order to know the "freshness" of these columns?
For instance, "for the Person Y, the name has changed 2 days ago, and the location 2 weeks ago".
Is this simply a versioning problem?
Person v1 - name: A1, address: B1, bio: C1, updated_at: 4.days.ago
Person v2 - name: A2, address: B1, bio: C1, updated_at: 3.days.ago
Person v3 - name: A2, address: B1, bio: C2, updated_at: 1.days.ago

And after I look trough different versions to find the last one with a different column?
Or is there better ways to handle that? Put freshness data in another table? Or even DB?
I will normally use MySQL, unless this can be solved by means of another database management system.
Regards.
Additional details
Thank you for you comments and answers, but I was forgetting something: Actually, I need to know when the column value was updated, but by who as well.
Something like "updated at XX by User Y".

With MariaDB 10.3 you can use system-versioned tables.

You can then see all the versions of a particular row with a query like:

SELECT *, ROW_START, ROW_END FROM Person WHERE id = 4 FOR SYSTEM_TIME ALL;

Also, with the AS OF syntax you can get the version of the row that existed at a particular time:

SELECT * FROM Person WHERE id = 4 FOR SYSTEM_TIME AS OF TIMESTAMP '2019-08-08 15:10:00';

See also this post in the MariaDB blog: Automatic Data Versioning in MariaDB Server 10.3.

EDIT

If you need to know who made a change, this will probably have to be controlled through your software. You can add a special last_change_by column in the table e.g. something like:

ALTER TABLE Person ADD last_change_by varchar(100);


MySQL chain replication timing of writes on first slave With a MySQL chain replication setup, e.g. M1->S1->S2, what exactly is the order of operations for writing to the first slave?  Is the binary log from the master simply streamed directly through to the second slave?  Or are database updates to the first slave committed before replicating those updates to the second slave?

The documentation for --log-slave-updates states This option causes the slave to write the updates performed by its SQL thread to its own binary log.  It's not clear to me if this is done in parallel or only after data is written.  I'm only interested in row-based replication, if that matters.

I ask because I have the unusual situation of a custom application acting as a second slave and operating on events that come through the binary log.  It runs queries on the first slave.  If the log events were to get to the application before writes on the first slave are committed, it will be operating on stale data.

To answer this, you need to understand the way MySQL replication works and the difference between the IO_thread and the SQL_thread.

In a nutshell, the IO_thread pulls events from the master and stores in the relay_log. The SQL_thread pulls from the relay_log and applies them to the slave dataset.

So, the description of --log-slave-updates (emphasis mine):

  This option causes the slave to write the updates performed by its SQL thread to its own binary log

means that the MySQL is only logging to the first slave's binary log after it is being committed on that slave. And it won't be available to the second slave until it has been committed on the first slave.

So if I'm understanding you right, you should be safe in your us-ecase.

Single-threaded sql thread and slave lag

It is important to note that by default, the SQL thread is single-threaded. This means even though your master can apply changes across multiple connections very quickly, when it is applied to the first slave it is one change at a time. This can contribute to slave lag.

Luckily, in MySQL 5.6 the ability to have multiple slave thread appliers was introduced with the --slave-parallel-workers option.

However, this only helped if you had multiple schemas(databases), because MySQL would only apply in parallel if statements were in different schemas.

This limitation was removed in 5.7 to allow for parallel application within the same schema.

The main point I am trying to make, is that if you are dealing with slave lag on the first slave, the second slave will take similar time (assuming similar hardware) so will be even further behind your master. So ensure your custom application takes that into account.


Take database backup of a SQL server 2012 from local SQL Server 2008 R2 I have a SQL Server 2012 installation that has some issues with SQL Server Agent because of which I cannot schedule a job or a maintenance plan to take backup.

What I am trying to do is to create a SQL Server job on SQL Server 2008 R2 and take backup of the SQL Server 2012 databases. Can someone suggest how to achieve this?

You should fix the SQL agent first.

as a temporary workaround, you can use SQLCMD and schedule it using windows scheduler for doing backups.

no need to spawn a job from different server and do the backup on another server.


Deadlock graph with a lock on an index on a seemingly unrelated table I have a deadlock graph from a deadlock where one process is doing a SELECT and one is doing an UPDATE.  This seems like the classic case where the SELECT gets a NCI lock to perform a join and then a CI lock to retrieve all the data by lookup.  And the UPDATE is using the CI lock to perform an update and then needs to lock an NCI because the update results in a status change and the NCI facilitates finding items by status.

The problem is that one of the locks the UPDATE wants is NOT on the table it is updating and I can't find why this is happening.

Here's the SELECT:

SELECT *,
       RIGHT(c.CC_NUMBER, 4) AS CC_LAST_4,
       DATEDIFF(ss, '1970-01-01', plan_started ) plan_started_epoch,
       DATEDIFF(ss, '1970-01-01', plan_expires ) plan_expires_epoch
FROM customers c, accounts a, parent_cos pc, htt_customers_overlay_ultra u
WHERE c.customer_id = a.customer_id
AND   u.customer_id = c.customer_id
AND   a.cos_id=pc.cos_id
AND   u.customer_id = 9300;

Here's the UPDATE:

UPDATE htt_customers_overlay_ultra SET plan_state = 'Active'  WHERE customer_id = 9300;

But according to the deadlock graph, the UPDATE is acquiring a lock on ACCOUNTS.ACCOUNT0, which is the PK (CI) of the ACCOUNTS table.  There are no foreign keys in the overlay table.  There are some default constraints which I don't currently have permission to see.

I've looked at the deadlock graph in SSMS and in SQL Sentry Plan Explorer Pro and am none the wiser.

Here are the execution plans:

For the SELECT

For the UPDATE

I'd like to find out why it is getting this lock, and then the best way to serialize these calls.

Things I am aware of which I have already advised the client which have bearing on the locks taken, but don't explain the seeming unrelated lock which is arising:

Remove * and identify the columns needed and alter the NCIs to become covering - this would potentially make the SELECT use fewer locks

Determine why the system is SELECTing the same data that another process is processing - this would potentially mitigate these two processes running at the same time at all

There is a table scan in the SELECT

The UPDATE query has an X lock on a key on "dbo.ACCOUNTS" blocking the SELECT from getting an S lock.

The SELECT query has an S lock on a key of htt_customers_overlay_ultra. The UPDATE query has a U lock on the same key and is blocked trying to convert that to an X lock.

The execution plan for the UPDATE doesn't feature Accounts at all so there is no obvious reason for it to have a key lock on Accounts. The Update transaction starts 0.01 seconds before the batch does. 2013-01-13 08:49:30.213 vs 2013-01-13 08:49:30.223.

Perhaps there was a preceding statement in a different batch (hence not shown in the deadlock graph) that actually started the transaction and acquired the mysterious X lock.