Cities and States (a few data): single table or splitted? I am designing a DB schema and I need to store cities and states. I expect only a few rows, let's say, 20 cities. Example:

id  | city           | state
1   | Rio de Janeiro | RJ
2   | Niteroi        | RJ
3   | Cabo Frio      | RJ
4   | Nova Friburgo  | RJ
5   | Campos         | RJ
6   | São Paulo      | SP
7   | Santos         | SP
8   | Santo André    | SP

As I said, less than 20 cities and only 2 or 3 states ( CHAR(2) ). Regarding performance, which is the best: A single table like the above example, or 2 tables with relationship between both?

PS: I'm not expecting any SELECT ... WHERE 'state'... I'll only select by city and then display which state it belongs to.

Thanks

CHAR(2) CHARACTER SET latin1 takes only 2 bytes.  You are not going to improve significantly on that by using an id TINYINT UNSIGNED (1 byte) to 'normalize' it.

Splitting into 2 tables is something I call "over-normalizing".

JOINs are pretty cheap, but they are not free.  This is a disadvantage of the 2-table approach.

While you are asking this question, I suggest you implement it both ways, see how big the tables are, see how fast the queries are, etc.  Use it as a learning exercise.

Yes, there will be other cases where 2 tables is better.


Import database Mysql structure Only I have a huge  dump.sql Mysql database of about 3GB that has the structure and the data

3 GB dump isn't huge. You can set up the MySQL locally, then import that dumpfile and then dump the base again by mysqldump with --no-data option.


How do You concatenate a value to an array that was retrieved through a query in sql? I have a table named "mkvtable" that establishes a correspondence between individual strings and arrays of strings:

  word   |     mkvword
---------+-----------------
 hello   | {world,friend}
 goodbye | {home,forever}
 hi      | {fellas,ladies}

According to the documentation provided for PostgreSQL, appending a value to an array of values should be a simple matter of using the concatenation operator: "||".

However, if, I attempt to add the string there, to the array of strings {fellas,ladies} which correspond to the word hi, nothing appears to happen:

SELECT mkvword FROM mkvtable WHERE word = 'hi' || 'there';

PostgresSQL appears to be saying as much:

 mkvword
---------
(0 rows)

Since there are no errors thrown, I assume things are syntactically okay.

What am I doing wrong? My best guess is that psql is getting upset that I'm trying to access the array by using it's relationship to a value from the adjacent column, but that's just an uneducated guess. Perhaps this sort of query doesn't jell well with the concatenation operator?

TL;DR: How do you properly concatenate values to arrays stored in table rows?

However, if, for example, I attempt to add the string 'there', to the array of strings '{fellas,ladies}' which correspond to the word 'hi', nothing appears to happen:

You are comparing the value of the column word with the string constant 'hithere' - you are concatenating two string constants, not a string to an array column.

 where word = 'hi' || 'there';

is the same as

 where word = 'hithere';

It sounds as if you really want:

SELECT mkvword || 'there'
FROM mkvtable
WHERE word = 'hi';

If you want to change the value that is stored in the table rather than just displaying it, you need an update:

update mkvtable
  set mkvword = mkvword || 'there'
where word = 'hi';


sp_AskBrent not logging queries when saving to table vs. sp_WhoIsActive sp_AskBrent, when run as an agent job every 5 minutes is logging to a table but never seems to populate QueryText. However, sp_WhoIsActive, when also run every 5 minutes on the same SQL Server instance does capture the queries. I'll get an Execution Plan (QueryPlan) every so often, but that only seems to show up during MP tasks like DB backups.

Am I invoking sp_AskBrent incorrectly, e.g. do I need to add an additional parameter? If so, which parameter(s)?

Here's what I'm using in job step of the agent job:

EXEC sp_AskBrent @OutputDatabaseName = 'DbaTools', @OutputSchemaName = 'dbo', @OutputTableName = 'AskBrentResults'

Does this functionality not exist in sp_AskBrent? I'm using AskBrentVersion 23 according to the table results). Didn't think BlitzFirst was anything but a name change, so I'm using what I thought was the latest sp_AskBrent.

Am testing both sp_AskBrent and sp_WhoIsActive run periodically to see which may be better for having a general idea of what's going on with my instance over time. Seems that sp_AskBrent wants to show me its summation of issues only when logging to a table.

Here's how I invoke sp_WhoIsActive as a job step in an agent job and it's returning query text and execution plans:

DECLARE @destination_table VARCHAR(4000) = '[DbaTools].dbo.WhoIsActiveResults' ;
EXEC dbo.sp_WhoIsActive @get_transaction_info = 1, @get_plans = 1,
            @destination_table = @destination_table ;

Has running one or both of these scripts every 5 to 10 minutes as an agent job provided proven value / saved your bacon in terms of follow-up troubleshooting when something went wrong after hours? I realize these scripts are typically run ad hoc in response to users asking about SQL Server "being slow." But I find the logging to table options intriguing.

Thanks for using our free scripts. You should really be using sp_BlitzFirst now, not AskBrent. That aside, what your command is missing is the table names for various performance counters. This is the 'full' command, which you can pare down to whichever result sets you're interested in.

EXEC sp_BlitzFirst @OutputDatabaseName = 'DBATools', @OutputSchemaName = 'dbo', @OutputTableName = 'BlitzFirstResults', @OutputTableNameWaitStats = 'BlitzFirstResults_WaitStats', @OutputTableNameFileStats = 'BlitzFirstResults_FileStats', @OutputTableNamePerfmonStats = 'BlitzFirstResults_PerfmonStats'


SQL Server Concurrent Insert/Update and Storing Data From: SQL Server 2014 Concurrent input issue

Follow up question:

Table Orderline stores information we receive from shops. OrderLineID is the unique, primary key in the table, since orders have multiple line items.

Orders can be updated, so we first check if OrderLineID is present in the table, and insert or update accordingly.

Due to customer modifications, line issue, server busy, etc, the OrderLineId can be sent more than once. We have a time Lastmodified column. If the incoming timestamp is older, it has no effect on the table. We work in a multithreading, parallel processing environment.
Utilize SQL 2016 Read Committed Snapshot Isolation level.

Previous question gave this response:

BEGIN TRANSACTION;

IF NOT EXISTS
(
    SELECT *
    FROM OrderLine WITH (UPDLOCK, SERIALIZABLE)
    WHERE OrderLineID=@OrderLineID
)
    INSERT INTO Orderline () VALUES ()
ELSE
    UPDATE Orderline
    SET ...
    WHERE OrderLineID=@OrderLineID
    AND LastModified<@CreatedTime;

COMMIT TRANSACTION;

New question:
Sometimes I do not have the OrderLineId initially, so we search by customer LastName, FirstName and ProductDescription and other columns (we have an old legacy system, don't bother explaining). I want to validate setting variables during these transactions will not affect anything. I will need the @OrderLineId for a future sql item, and wanted to hold value. I executed sp_lock to test, seems to work. I may just save the value after transaction is complete, but did not want to relook it up again, for optimization purpose.

Method 1: Save variable all at once

begin transaction

declare @OrderLineId int

set @OrderLineId =
( SELECT OrderLineId
  FROM Orders WITH (UPDLOCK, SERIALIZABLE)
  WHERE LastName = @LastName
  AND FirstName = @FirstName
  AND WebLoginDate = @WebLoginDate
  AND ProductName = @ProductName )

IF @OrderLineId is null
BEGIN
   INSERT INTO Orderline () VALUES ()
   SET @OrderLineId = SCOPE_IDENTITY()
END
ELSE
BEGIN
    UPDATE Orderline
    SET ...
    WHERE OrderLineID=@OrderLineID
    AND LastModified<@CreatedTime;
END
COMMIT TRANSACTION;

Method 2: is this method safer?

BEGIN TRANSACTION;

declare @OrderLineId int
IF NOT EXISTS
(
    SELECT *
    FROM Orderline WITH (UPDLOCK, SERIALIZABLE)
    WHERE LastName = @LastName
    AND FirstName = @FirstName
    AND WebLoginDate = @WebLoginDate
    AND ProductName = @ProductName
)
BEGIN
   INSERT INTO Orderline () VALUES ()
   SET @OrderLineId = SCOPE_IDENTITY()
END
ELSE
BEGIN

set @OrderLineId =
(  SELECT OrderLineId WITH (UPDLOCK, SERIALIZABLE)
   FROM Orderline
   WHERE LastName = @LastName
   AND FirstName = @FirstName
   AND WebLoginDate = @WebLoginDate
   AND ProductName = @ProductName)

    UPDATE Orderline
    SET ...
    WHERE OrderLineID=@OrderLineID
    AND LastModified<@CreatedTime;
END
COMMIT TRANSACTION;

The updated scripts in your question are equally safe.

Expanding on the answer to your other question, you could use the natural key in both WHERE clauses and assign the @OrderLineID variable in the UPDATE statement. Perform the conditional insert first if that is the most common case, otherwise perform the update first. This method will eliminate the SELECT query.

BEGIN TRANSACTION;

DECLARE @OrderLineId int;
INSERT INTO Orderline (...)
    SELECT ...
    WHERE NOT EXISTS (
        SELECT *
        FROM Orderline WITH (UPDLOCK, SERIALIZABLE)
        WHERE LastName = @LastName
            AND FirstName = @FirstName
            AND WebLoginDate = @WebLoginDate
            AND ProductName = @ProductName
    );
IF @@ROWCOUNT > 0
BEGIN
    SET @OrderLineId = SCOPE_IDENTITY();
END
ELSE
BEGIN
    UPDATE Orderline
    SET
         ...
         , @OrderLineId = OrderLineId
    WHERE LastName = @LastName
        AND FirstName = @FirstName
        AND WebLoginDate = @WebLoginDate
        AND ProductName = @ProductName
        AND LastModified<@CreatedTime;
END;

COMMIT TRANSACTION;

Be aware that when you mix using the natural key and surrogate key to locate rows, deadlock likelihood is increased.


Speeding up mysqldump / reload Converting a large schema to file-per-table and I will be performing a mysqldump/reload with --all-databases. I have edited the my.cnf and changed "innod_flush_log_at_trx_commit=2" to speed up the load. I am planning to "SET GLOBAL innodb_max_dirty_pages_pct=0;" at some point before the dump. I am curious to know which combination of settings will get me the fastest dump and reload times?

SCHEMA stats:

26 myisam tables
413 innodb
~240GB of data

[--opt= --disable-keys; --extended-insert; --quick, etc]
--no-autocommit ??

vs prepending session vars like:
"SET autocommit=0; SET unique_checks=0; SET foreign_key_checks=0;"

Are the mysqldump options equivalent or not really?

Thanks for your advice!

ASPECT #1

While setting innodb_max_dirty_pages_pct to 0 is good to do prior to a dump, you will have to wait until the dirty page count falls below 1% of the InnoDB Buffer Pool size. Here is how you can measure it:

SELECT ibp_dirty * 100 / ibp_blocks PercentageDirty FROM
(SELECT variable_value ibp_blocks
FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_total') A,
(SELECT variable_value ibp_dirty
FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_dirty') B;

Keep running this report until PercentageDirty reaches close to 1.00. Perhaps you could just set innodb_max_dirty_pages_pct to 0 one hour before the dump.

If you do not change innodb_max_dirty_pages_pct, a mysqldump will forec a flush of dorty blocks involving the table you are dumping.

ASPECT #2

You should not have to prepend "SET autocommit=0; SET unique_checks=0; SET foreign_key_checks=0;" because a mysqldump includes them at the beginning of the dump. Here is a sample mysqldump's header (Please note the two lines after TIME_ZONE)

-- MySQL dump 10.11
--
-- Host: localhost    Database: dbAccessData
-- ------------------------------------------------------
-- Server version       5.0.51a-community-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Current Database: `dbAccessData`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `dbAccessData` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `dbAccessData`;

ASPECT #3

Please run this query

SELECT engine,count(1) table_count FROM information_schema.tables
WHERE table_schema='mysql' GROUP BY table_schema;

I ran this and got 25 for MySQL 5.5.23. Since you have 26 you have only 1 tables outside the mysql schema. To find it, run this:

SELECT table_schema,count(1) table_count FROM information_schema.tables
WHERE engine='MyISAM' GROUP BY table_schema;

If you stop writing to the one lone table, you should be able to mysqldump all databases just fine.

ASPECT #4

All the needed options for --opt are adequate. No need to alter it.

ASPECT #5

You may want to dump the databases into different file: Please see my Apr 17, 2011 post How can I optimize a mysqldump of a large database? on how to script parallel mysqldumps.


pt-table-sync: syncing slave from master that has different credentials? I have a MySQL slave under my full control and a master server where I have only read-only login. Also, logins are different. At some point replication have stopped due to some kind of problem.

I would like to use pt-table-sync (https://www.percona.com/doc/percona-toolkit/2.2/pt-table-sync.html)  to restore sync between master and slave. However, by default it tries to use same credentials for master and slave. I use --sync-to-master (so it requires only one DSN from command line and attempts to apply its user/password to master that is found out from the slave DB)

How I can tell pt-table-sync to use different credentials to slave and master?

Yes you can do that, there are switches for the CLI so these will hopefully get you correct access to the slave...

--slave-user and --slave-password

Here is a link to the specific part of the documentation that provides this information

https://www.percona.com/doc/percona-toolkit/LATEST/pt-table-sync.html#cmdoption-pt-table-sync-slave-user

Please note at the top of that page:

  This tool changes data, so for maximum safety, you should back up your data before using it.

Disclosure: I work for Percona


How to add External data source into MySQL? I have two database. One is a FileMaker database and the other one is mysql. I want to use this database in mysql. I created ODBC data base connection so that I can sync both database e.g when I make changes in mysql database then FileMaker database should also be updated. Is this thing possible in mysql? If not then in which open source database this thing is supported?

You cannot do what you're asking (i.e.: replicate content from MySQL to FileMaker). FileMaker doesn't have (as of current version) any way to automatically replicate data from external sources.

Instead of that, you can do something different with equivalent result: access your MySQL data directly from FileMaker. This means FileMaker never has a copy of the data. You just have one data source (MySQL), and have FileMaker SELECT, INSERT, DELETE or UPDATE that MySQL table directly.

You can do that for as many tables on as many MySQL databases as necessary.

You do this by using in FileMaker an "external data source". Check Connecting to data sources from FileMaker.

NOTE: This has been available in FileMaker since (as far as I can remember) version 12. FileMaker allows also connections to other RDBMS such as Microsoft SQL server and Oracle. Newer versions of FileMaker allows also external sources to be PostgreSQL and others (See here)


Import 10 Billion Rows, Daily I need to import 10 billion rows into a database, daily.

SSIS, doesn't seem like its cutting it, and I'm starting to think that writing some kind of custom C/C++ code might be an option (I don't much know C/C++) to try and do it.

I'm working with a standard level production box, nothing fancy but decent.  SQL 2012.

Any advice is welcome.

EDIT:

Source is a flat file.
Say 20 columns.  Not sure about size.
Source data is on the same disk.  (At the moment).

EDIT 2:
While the columns are largely int/bigint, there are 5-7 VARCHAR(x) (the largest is VARCHAR(1000)).

The problem im finding with SSIS is that the read/write speeds are not what I'm hoping for. I've tried fastParse, etc, and various other techniques.

Other than tips, I'm hoping for techniques.  Tips still welcome obviously.

Look into BCP or BULK INSERT, both of which can share format files.

Quick math based on pure guesses, since no hard details are given:

13 numeric columns, pretend they're 6 bytes each on average = 13*6=78 bytes.

5 VARCHAR columns, pretend they're an average of 300 bytes each based on the "largest" being VARCHAR(1000) per the original question = 5*300 = 1500 bytes

1500 + 78 = 1578 bytes per row average.

1578*10000000000/(1024*1024) = 15048981 Megabytes per day

15048981/(24*60*60) = 174MB/s EACH WAY (once from the source and once to the database) if you load it continuously, 24 hours a day, rain or shine, during index maintenance, during backups, during DBCC CHECKDB and OS level disk defragmentation.

If you have 7 VARCHAR fields at an average of 800 bytes each, that's 40 times the bandwidth requirements of 5 VARCHAR fields at an average of 20 bytes each; the latter can be done over gigabit ethernet, the former cannot given your 10 billion rows a day, every day criteria.

Figure out what your data sizes actually are, and what your time windows actually are, and then do the math on how fast you need it to go for real.

If you have to, you can try disabling or removing triggers, traces, constraints, foreign keys, indexes, etc. on whatever table you're putting this data into.

And watch out for backup space requirements as well :).


Around 50 % of the tables in a certain SQL Server database have either zero, one or two rows - is this a sign of a good design? First off, I'm not a database programmer, nor admin or anybody who is responsible for this area, so I might be wrong in many of the following questions/assumptions.

I've recently encountered a DB schema of about 250 tables. It's a database for an eshop. I've looked into many of these tables and was surprised about how many of them were completely empty or had just one or two rows. So I checked all the tables with:

select count(*)
from sysobjects t, sysindexes i
where t.xtype = 'U' and i.id = t.id and i.indid in (0,1)
where i.rows in (0, 1, 2);

and

select count(*)
from sysobjects t, sysindexes i
where t.xtype = 'U' and i.id = t.id and i.indid in (0,1);

I found out that about 50 % of all the tables are tables with 0, 1, or 2 rows.

I understand that there are some rules for creating databases (normal forms), but to me this just seems too much. For example, wouldn't it be a bit more efficient to store some of this data in one table then in two or more and then having to use joins? The way I imagine this is the database would store data in one table together (close to each other) on the disk, so they could be retrieved fast. But if I use 2 or more tables (therefore I need to use joins when retrieving the data), the data could be stored on different places on the disk, therefore taking more time to retrieve them?

The database is not a big one I suppose, the largest tables have around 1 million rows, I think this could be considered small nowadays. So it might be that on this amount of data, it doesn't make a big difference whether I use joins extensivelly.

I know my question is rather broad. Since I don't have more knowledge about the database design, I can't really be more presice, yet I'd appreciate some of your thoughts on this.

Thank you

How many of the tables are not connected to any other table by a FOREIGN KEY relationship? You can do this by checking out the answer here.

It is possible to have tables with 0 records - if you ran a nuclear power plant for example, you'd want the table catastropic_failure to have 0 records.

Some reference tables could only have 1 or 2 reference codes - maybe the devs thought they'd be added to later?

I would say you're looking at a problem of orphaned tables - you've heard of orphaned records - records with no parents in parent-to-child relationship? These tables been lost and forgotten in the development process.

It was somebody's "bright idea" one day but was subsequently vetoed by senior dev/management - but such was the hothouse and/or firefighting atmosphere in the big open plan room where all the devs worked, that these "bright ideas" were never removed from the code base.

It's also possible that these tables are useful for 1 or 2 clients but not used by the bulk of the system users/clients/whatever?

Another possibility is that you should be careful that the coding style of your establishment (maybe prior to your arrival?) wasn't to use various tables as temporary storage - they are used briefly to store bits and pieces (important nonetheless) of data which aren't properly cleared down until they are used the next time?

What I would do:

1) try and search through your APPLICATION code and see if you can find tables that have no references and 0 records in the code - delete those - TAKE BACKUPS FIRST.

Be especially careful of triggers which may refer to those tables and also stored procedures which reference them as well.

Do you have a test suite for your code - I'm guessing not?

Then do the same with those tables which have 1 and then 2 records. You will probably find a few that can't be deleted without provoking obvious problems. Then there are those which will cause problems which will pop up days/weeks/months/years later. The answer here is it depends on what risks you want to take.

Find the dev and/or managers who've been on the project longest and ask them about this issue - that's probably your best best. By my "rule of thumb" reckoning, you should have, at most, say 10% of your tables in the form you describe. However, unfortunately, it's going to be a difficult mess to clear up.

Best of luck with your project and welcome to the forum! :-)


Oracle Materialized View Error Log Table? I have a number of materialized views that for various reasons on (rare) occasion will fail to materialize. Is there a database table that stores the error messages from the refresh attempts that I can query?

You can use the following query to check when the MVs were last refreshed.

SQL> select owner, mview_name, last_refresh_type, last_refresh_date
from dba_mviews/user_mviews;

If you were refreshing these MVs using DBMS_SCHEDULER:-

SQL> select log_date, status
from dba_scheduler_job_run_details
where job_name='JOB_NAME';

LOG_DATE                                     STATUS
--------------------------------------       ------------------------------
16-APR-17 04.42.58.546973 AM +05:45          SUCCEEDED
08-APR-17 02.38.12.843886 AM +05:45          FAILED

If you were refreshing these MVs using Job Queues/DBMS_JOB`:-

SQL> select job, last_date last_refresh,next_date next_refresh, total_time,broken, failures, what
from dba_jobs
where what like '%dbms_refresh%';

The reason of the job failure is written in alert_<SID>.log file as shown below as well as in a trace file referenced in the alert log file.

Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_j003_143260.trc:
ORA-12012: error on auto execute of job "SCHEMA"."JOB_NAME"
ORA-12008: error in materialized view refresh path
ORA-22992: cannot use LOB locators selected from remote tables
ORA-06512: at "SCHEMA.PACKAGE/PROCEDURE", line 70
ORA-06512: at line 1


ora-49204: recursive dde invocation at phase i - after switchover to standby I am currently testing a data guard environment. After a switchover, which is performed successfully, I can see the following error in the alert.log of the new stand:

ora-49204: recursive dde invocation at phase i
This error is followed by ora-01110.
This happens for all data files.

The standby is running in open mode read only.
I checked the database for logical and physical block corruption via rman.
Everything looks good.

How do I have to interpret this error?
I am using the base version of Oracle 12cR2.

I would appreciate, if anyone could give me some hint.

Best regards

IronMike

Per Oracle Support, this is probably a bug
https://support.oracle.com/knowledge/Oracle%20Database%20Products/2322290_1.html

Function kcidr_file_header_check_common is File Header check which cause of this issue. If you see this in the Stack Trace and Standby opens Read only successfully then you are most likely encountering this bug.


Get list of all tables and columns "touched" by application I need to find out where in the database changes are made, originating from an application. In other words: I'm not interested in the actual value changes, I'm interested in where these changes are taking place. Can this be done and if so, how?
Context
I'm tasked with managing a gigantic application that interacts with a very large database. Even though I have the source code available it is proving quite difficult to track which changes in the database the different parts of the application cause. More specifically which tables and columns get "touched" by the actions invoked from the application.
I have a test system set up (a clone of production) with it's own database. I am the only user on the test system so I can safely say that triggered changes are caused by my own actions.
I've been reading up on CDC, but as far as I can tell if you don't know which columns are being touched you have to query ALL the columns by name. This is problematic considering the database holds hundreds of tables with hundreds of columns.
Is there any other way to get a list of changes in the database per action?

It sounds like, for this particular scenario, you should run a trace in SQL Profiler. You can open it from the Tools menu in SSMS or find it in the Start > Applications > SQL Server 2008 R2 > Performance Tools folder.

Create a new trace and connect it to your database. Use the "blank" template and in the Events Selection tab just select Stored Procedures > RPC:Completed and TSQL > SQL:BatchCompleted.

Pause (but not stop) your trace until you're ready to click something, then start the trace back up and see exactly what queries the application produces. If you're still getting a lot of noise in your traces, you can use the column filters and the CientProcessID or SPID fields will probably help you nail it down.


How to replace multiple unicode characters I have identified a couple of unicode characters in my database by means of the following query:

SELECT name FROM table WHERE name NOT LIKE '%'+CAST(name AS VARCHAR(1000))+'%'

...with which I get stuff like this:

MORRISON CPA LIMITED  E¦¬Ç¦®EŒÆŒƑÈ¨ˆÅ¸«Äº‹Å‹™Æ‰€ÆŒ‰E™Å…¬Å¸

Is it possible to get the collation when using a REPLACE function? Something like:

UPDATE table SET name = REPLACE(name,NOT LIKE '%'+CAST(name AS VARCHAR(1000))+'%','')

I know I can individually replace them REPLACE(name,'Æ','') but is there a way to replace everything that is unicode?

Edit: I don't know why but I get less rows if I use

SELECT name FROM table WHERE name != CAST(name AS VARCHAR(1000))

NVARCHAR - VARCHAR conversions

This topic has been discussed heavily in a previous question on DBA.SE

Some parts of the answers on that question:

  which characters can be stored in an 8-bit / non-Unicode encoding
  depends on the code page, which is determined by the Collation.

&

  VARCHAR can no longer be referred to as "non-Unicode". So, starting
  with the first public beta of SQL Server 2019 in September 2018, we
  should refer to VARCHAR as an "8-bit datatype", even when speaking in
  terms of versions prior to SQL Server 2019. This terminology holds
  true for all 4 types of encodings that can be used with VARCHAR:

  Extended ASCII Double-Byte Character Sets (DBCS) EBCDIC UTF-8
  (Unicode)

Solomon Rutzky

  From the comments, I agree "Extended ASCII" is really bad term that
  actually means a code page that maps characters/code points in the
  128-255 range, beyond the standard 0-127 code point range defined by
  ASCII.

Dan Guzman

Depending on your collation, some characters will be able to be stored in varchar / 8-bit datatype fields, some not.

  My values do not transform to '?' when casting to varchar, your fiddle
  uses sql 2017 could that be the reason?

This can depend on collation, and should be due to converting of certain characters

I am using SQL Server 2017, collation Latin1_General_CI_AS

On my test, the Ƒ character was changed to  ƒ instead of put as ? when converting nvarchar --> varchar.

Other than that, there are no different characters when comparing both, on my collation / version.

An example:

SELECT CAST([name] AS VARCHAR(1000)), [name]
FROM dbo.Bla
WHERE [name]
NOT LIKE '%'+CAST([name] AS VARCHAR(1000))+'%';

With the small difference on the F's.

(No column name)    name
ORRISON CPA LIMITED  E¦¬Ç¦®EŒÆŒƒÈ¨ˆÅ¸«Äº‹Å‹™Æ‰€ÆŒ‰E™Å…¬Å¸   ORRISON CPA LIMITED  E¦¬Ç¦®EŒÆŒƑÈ¨ˆÅ¸«Äº‹Å‹™Æ‰€ÆŒ‰E™Å…¬Å¸

You could compare the text here

Which makes the solution below not useful for you.

Some workarounds on values that fail to convert would be to

Cast the values as varchar(1000), and then remove the ? after validating that no ?'s exist.
Only update name values that do not have regular ?'s.
Replacing the ? to a value that cannot exist in your data and then
replacing it back.

On the premise that the unicode is changed to a ? when casting them to varchar

#1 Cast the values as varchar(1000), and then remove the ?.

SELECT [name] FROM [dbo].[Table]    WHERE [name] like '%?%';

Update if none found

 UPDATE [dbo].[Table] SET [name] = REPLACE(CAST([name] AS VARCHAR(1000)),'?','');

#2 Only update name values that do not have regular ?'s.

UPDATE [dbo].[Table]
SET [name] = REPLACE(CAST([name] AS VARCHAR(1000)),'?','')
WHERE [name] NOT LIKE '%?%';

DB<>Fiddle

#3 Replacing the ? to a value that cannot exist in your data and then
    replacing it back.

With you guessed it, another REPLACE.

UPDATE [dbo].[Table]
SET [name] = REPLACE(REPLACE(CAST(REPLACE([name],'?','THISVALUECANNOTOCCURINMYDATA') AS VARCHAR(1000)),'?',''),'THISVALUECANNOTOCCURINMYDATA','?')

ReplaceCeption.

DB<>Fiddle

If there are no other, more foolproof, get all unicode values and replace these workarounds. I would use #3 just to be sure that no original ?s are replaced.


Excluding log shipped DB from current database maintenance plan (Ola's solution) The Senior DBA and I were discussing our most recent rollout of log shipping to 25+ servers and one of the issues we had ran into during our pilot was the log chain breaking randomly.
We narrowed down several other factors that, at one point, were causing this issue (VEEAM backups were not using "copy only" in application-aware processing). However, after resolving many of the issues, one of our colleagues mentioned that we should remove the log shipped db from any maintenance plan we had in place.
But, and I cannot remember where, both my Senior DBA and I were under the impression that we had read SOMEWHERE that log shipped dbs were ignored by default. Despite this, we removed the log backups (including full and diff just to be sure) and that resolved the issue.
My question is this: Should we exclude the backups? And then the follow up question is whether they should be excluded from Ola's and/or MSFT's plans?
Regardless, our issue is resolved - this is just more of question of checking our sanity that we did read this somewhere.

When you set up Log Shipping, it creates a job for transaction log backup. If you already have an existing transaction log backup job, irrespective of kind (maintenance plan, Ola's solution, SQL Agent job) should be disabled/deleted.
There is a workaround to keep the existing transaction log backup job and set up Log Shipping. You can disable the job that gets created during the Log Shopping setup.
Sample code:
-- Execute the following statements at the Primary to configure Log Shipping
-- for the database [HostName\SQL2019].[DbaDatabase],
-- The script needs to be run at the Primary in the context of the [msdb] database.
-------------------------------------------------------------------------------------
-- Adding the Log Shipping configuration

-- ****** Begin: Script to be run at Primary: [HostName\SQL2019] ******

DECLARE @LS_BackupJobId AS uniqueidentifier
DECLARE @LS_PrimaryId   AS uniqueidentifier
DECLARE @SP_Add_RetCode As int

EXEC @SP_Add_RetCode = master.dbo.sp_add_log_shipping_primary_database
        @database = N'DbaDatabase'
        ,@backup_directory = N'\\logshipping\backup'
        ,@backup_share = N'\\logshipping\backup'
        ,@backup_job_name = N'LSBackup_DbaDatabase'
        ,@backup_retention_period = 4320
        ,@backup_compression = 2
        ,@backup_threshold = 60
        ,@threshold_alert_enabled = 1
        ,@history_retention_period = 5760
        ,@backup_job_id = @LS_BackupJobId OUTPUT
        ,@primary_id = @LS_PrimaryId OUTPUT
        ,@overwrite = 1

IF (@@ERROR = 0 AND @SP_Add_RetCode = 0)
BEGIN

DECLARE @LS_BackUpScheduleUID   As uniqueidentifier
DECLARE @LS_BackUpScheduleID    AS int

EXEC msdb.dbo.sp_add_schedule
        @schedule_name =N'LSBackupSchedule_HostName\SQL20191'
        ,@enabled = 1
        ,@freq_type = 4
        ,@freq_interval = 1
        ,@freq_subday_type = 4
        ,@freq_subday_interval = 15
        ,@freq_recurrence_factor = 0
        ,@active_start_date = 20201029
        ,@active_end_date = 99991231
        ,@active_start_time = 0
        ,@active_end_time = 235900
        ,@schedule_uid = @LS_BackUpScheduleUID OUTPUT
        ,@schedule_id = @LS_BackUpScheduleID OUTPUT

EXEC msdb.dbo.sp_attach_schedule
        @job_id = @LS_BackupJobId
        ,@schedule_id = @LS_BackUpScheduleID

EXEC msdb.dbo.sp_update_job
        @job_id = @LS_BackupJobId
        ,@enabled = 0

END

EXEC master.dbo.sp_add_log_shipping_alert_job

-- ****** End: Script to be run at Primary: [HostName\SQL2019]  ******


PHP MYSQL Update Two Tables Using Cross Joins I am running into a problem updating my two tables at the same time.
My first table res which needs to be updated every time the other table
rest gets input from a HTML Form.

Table res=> http://sqlfiddle.com/#!9/82bfc2/2 . column id =>Primary Key
This table holds all the information related to Train Journey. This has columns(ie. col AC_I=no. of tickets at the begging, book= tickets booked and bal=balance of tickets from the total tickets)
 which describe against which Train how many tickets are available, how many were booked and what's the current balance when the user is logged in to book their tickets.

My Second table rest  http://sqlfiddle.com/#!9/557e4. column PNR =>Primary Key
This table takes input from users.

My question is how to update table res and update columns which is created to store the tickets booked for each class and for a particular train name or train number.

For example: IF customer books a seat in TRAIN NAME ='A', CLASS='AB' then my other table with the same row having a column "BOOK" will auto-increment.

What I tried is My SQL Trigger and Join.
Both are not working at this time.

Trigger=>

DELIMITER $$
DROP TRIGGER IF EXISTS t1i $$
CREATE TRIGGER t1
  AFTER INSERT ON rest
  FOR EACH ROW
  BEGIN
    UPDATE res
      SET book = 1
      WHERE Tr_Num = NEW.Tr_Num;
  END $$
DELIMITER ;

JOIN=>

UPDATE res
JOIN rest ON res.id = rest.PNR and res.TRAIN_NUMBER=rest.Tr_Num
SET res.book = 1
where case when rest.class ='AC I' then res.book=1
           when rest.class ='AC II' then res.book=1
           when rest.class ='AC III' then res.book=1
      end

Result shows 0 Rows affected

Kindly assist.

You can do an UPDATE of multiple tables in a single UPDATE statement, if that is really what you want to do here. It would be something like this for a specific 'rest' row (PNR = 5):

UPDATE res
INNER JOIN rest ON res.id = rest.PNR and res.TRAIN_NUMBER=rest.Tr_Num
SET
  res.book = res.book + 1,
  rest.age = 21
WHERE (rest.class ='AC I' OR rest.class ='AC II' OR rest.class ='AC III') AND rest.PNR = 5;

However, and maybe I misunderstand, but I suspect what you really want to do is actually an INSERT + an UPDATE in a single transaction. You want to INSERT a new row into 'rest', and then update the relevant numbers in 'res'.

To do this, turn off autocommit in PHP, then INSERT INTO rest ... and UPDATE res ..., and then execute a COMMIT. Make sure to catch any DB-related exceptions, and handle them by executing ROLLBACK instead of COMMIT.

This way, the operation is atomic. Other DB sessions looking at the tables will always see the results of either none or all of the updates in the transaction (assuming you're not using the non-default READ UNCOMMITTED transaction isolation level).

There is not really a good way to do an INSERT and an UPDATE in a single query, except you could wrap the two statements in a stored procedure, and then call that stored procedure to execute both statements in one go. Refer to the documentation for the CREATE PROCEDURE syntax for details and/or see e.g. a stored procedure tutorial at w3resource.com. Note that it's still a good idea to wrap the two statements in a transaction inside the stored procedure.


Get the most recently completed step for each SQL job I'm trying to get a list of jobs and their most recently completed step from the MSDB database. For a single job, this is pretty straight forward, something like:

 SELECT TOP 1 j.job_id, j.name, h.step_name, h.run_date, h.run_time, h.step_id
 FROM msdb.dbo.sysjobs j
 INNER JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id
 WHERE j.name = 'my favorite job'
 ORDER BY h.run_date DESC, h.run_time DESC, h.step_id DESC

But how can I get a list of ALL jobs and their most recently completed step?

Note this is SQL 2000, so I can't use PARTITION OVER.
I also can't do a SELECT MAX() because its not a single RunDateTime field, I have to sort descending by two fields (OrderDay, OrderTime).

Edit: Probably not possible, but I'd love to be able to something like:

SELECT j.job_id, ...
FROM msdb.dbo.sysjobs j
INNER JOIN (SELECT TOP 1 step_name, run_date, run_time, step_id
            FROM msdb.dbo.sysjobhistory
            WHERE job_id = j.job_id
            ORDER BY run_date desc, run_time desc, step_id desc) as H

or something, but it doesn't seem to like syntax like that...

Unfortunately because there can be ties (the second-to-last step can take 0 seconds and therefore finish at "exactly" the same time as the last step), this is really not pretty in SQL Server 2000. Rather than calculate all of the datetime stuff multiple times I think the easiest way is to stuff it into a #temp table. You may consider creating a permanent view with the datetime conversion.

SELECT job_id, step_name, run_date, run_time, step_id,
    run_datetime = CONVERT(DATETIME, RTRIM(run_date) + ' ' +
    STUFF(STUFF(RIGHT('00000' + RTRIM(run_time), 6),3,0,':'),6,0,':'))
  INTO #t
      FROM msdb.dbo.sysjobhistory;

SELECT j.job_id, j.name, t.step_name, t.run_date, t.run_time,
       t.step_id, t.run_datetime
FROM #t AS t
INNER JOIN
    (
      SELECT job_id, run_datetime = MAX(run_datetime)
      FROM #t GROUP BY job_id
    ) AS m
    ON m.job_id = t.job_id
    AND m.run_datetime = t.run_datetime
INNER JOIN
    (
      SELECT job_id, run_datetime, step_id = MAX(step_id)
      FROM #t GROUP BY job_id, run_datetime
    ) AS s
    ON s.job_id = m.job_id
    AND s.run_datetime = m.run_datetime
    AND s.step_id = t.step_id
INNER JOIN
    msdb.dbo.sysjobs AS j
    ON t.job_id = j.job_id
    AND m.job_id = j.job_id
    AND s.job_id = j.job_id;

DROP TABLE #t;


Need to delete consecutive, unchanged records I have a rather large MySQL db that is tracking pricing changes for a large number of products.  Each entry has a SKU a datetime and a price.  Sometimes, there is no change in the price from one time period to the next.

I am trying to figure out a way to more efficiently store this data.  I would like to delete any instances where the price didn't change.  For example, if a product was $10 on march 10, 13, and 16th, then it went to $12 on the 19th, then we have no need to store those duplicate prices on the 13th and 16th.  We can just delete those entries, and when presenting data, we will assume no entry for those dates means no change in price.

I am thinking the best way to do this is to run a script to clean up the table and remove these duplicates every day or so.  Would there be any way to create a mySQL query that can do this?  Otherwise, I guess I'll just need to write a script that will cycle through the whole table and do it with multiple queries.

edit:  more detail

Here is my table:

+----+-----+----------------+-------+
|   id | sku |  date             | price |
+----+-----+----------------+-------+

id is the auto-increment primary key.


Oracle TNS Permission denied when creating database I hope you can help me.

I need to restore an Oracle 9i (9.2.0.6.0) database on a Windows Server 2003 R2 x64 from a cold backup.
I managed to get the 9i installer and the version patch (since I was told I need the exact Oracle version to restore cold backups).

I installed 9i and the patch, but the Database Configuration Assistant throws an "ORA-12546:TNS Permission denied" error when creating the database (In the first step "Creating and starting Oracle instance").

Windows firewall is disabled, and my user (Administrator) is in the ORA_DBA group.

I'm new to Oracle, therefore I don't know which steps should I follow in order to solve this, and I haven't been able to find any solution on the web (maybe because 9i support ended long time away). I'm assuming if I can't even create a database, I won't be able to restore the cold backup either. Please help

Thanks in advance

Please, verify if you are installing Oracle 32bit or 64 bit.
This is important because Oracle in Metalink Knolwedge Base says:

  Getting ORA-12546 ORA-12560 and ORA-516 while creating the database
  (Windows64) (Doc ID 365852.1)

  You can run Oracle 32bit, on Windows 32bit.  OR  You can run Oracle
  64bit, on Windows 64bit.

  You can not mix the versions.

Also, report the complete error stack.
Normally Oracle zip files have 32 or 64 bit eported in the name of the archive itself.
 Here you can find a lot of way to identify the executables architecture in Windows:
How to check if a binary is 32 or 64 bit on Windows?

Regards

Giova


finding the most recently updated record with multiple date columns I have recently inherited a SQL Server 2016 database which was converted from an old MS Access db. I have a table that shows various repair dates and test dates for associated serial numbers. I would like to have a single row  per serial number with all of the most recent dates shown.  The table right now is showing multiple rows per serial number depending on the various dates  entered into the database.

Repair and Config Date Table:

+------+-------------+-----------------+---------------+
| S/N  | REPAIR_DATE | INSPECTION_DATE | REPLACED_DATE |
+------+-------------+-----------------+---------------+
| 1001 | 2017-11-17  | 2017-11-17      | NULL          |
| 1002 | NULL        | NULL            | NULL          |
| 1002 | 2016-11-21  | 2016-11-21      | NULL          |
| 1004 | NULL        | NULL            | NULL          |
| 1004 | NULL        | 2017-03-28      | 2017-09-07    |
| 1004 | 2017-12-15  | NULL            | NULL          |
+------+-------------+-----------------+---------------+

Desired Output

+------+-------------+-----------------+---------------+
| S/N  | REPAIR_DATE | INSPECTION_DATE | REPLACED_DATE |
+------+-------------+-----------------+---------------+
| 1001 | 2017-11-17  | 2017-11-17      | NULL          |
| 1002 | 2016-11-21  | 2016-11-21      | NULL          |
| 1004 | 2017-12-15  | 2017-03-28      | 2017-09-07    |
+------+-------------+-----------------+---------------+

How can I combine the table results so only one row of serial number is showing the latest dates from each column?

You can get the aggregates, MAX(Date) in this case, by using GROUP BY:

Quoted from MS Docs:

  A SELECT statement clause that divides the query result into groups of rows, usually for the purpose of performing one or more aggregations on each group. The SELECT statement returns one row per group.

SELECT
    SerialNumber,
    MAX(Repair_Date) as Repair_Date,
    MAX(Inspection_Date) as Inspection_Date,
    MAX(Replaced_Date) as Replaced_Date
FROM
    YourTable
GROUP BY
    SerialNumber


mysql_select_db() very slow on a busy server I have a MySQL Server with about 30 Databases and 1500 Tables. Overall size of the data is 2 GB. The Server has 16 GB RAM and an AMD FX-6100 CPU. Most tables are myisam.
On average the server answers 675 queries per second, but in the afternoon it gets easily into 20k queries per second. The load of the machine barely gets over 1. So it looks like the server is just running along, singing and full of joy.

But in my PHP/Nginx stack I see over 50k cases per day where the PHP needed longer than 1 second to do its work. The other 2m PHP requests of that day finished in 40 to 150 milliseconds. I started to profile my PHP code and I can see that sometimes one call to mysql_select_db() needs over 100 milliseconds. Because mysql_select_db() gets called multiple times, this can stack up to 15 seconds wall time or more :(

PHP processes are running on multiple machines, but not on the MySQL server. I have up to 1000 active & persistent connections. The machines are connected by 1G Ethernet, traffic between them is 60Mbit maximum.

So I'm suspecting that I'm doing something wrong on my MySQL config. It feels like I'm driving a race car but with a locked handbrake.

This is my config:

# MyISAM #
key_buffer_size                = 1G
myisam_recover                 = FORCE,BACKUP

# SAFETY #
max_allowed_packet             = 16M
max_connect_errors             = 1000000
skip_name_resolve
sql_mode                        = NO_UNSIGNED_SUBTRACTION
innodb                         = FORCE

# CACHES AND LIMITS #
tmp_table_size                 = 32M
max_heap_table_size            = 32M
query_cache_type               = 0
query_cache_size               = 0
max_connections                = 1700
thread_cache_size              = 100
open_files_limit               = 65535
table_definition_cache         = 4096
table_open_cache               = 10000

# INNODB #
innodb_flush_method            = O_DIRECT
innodb_log_files_in_group      = 2
innodb_log_file_size           = 256M
innodb_flush_log_at_trx_commit = 2
innodb_file_per_table          = 1
innodb_buffer_pool_size        = 2G

# LOGGING #
log_error                      = /var/log/mysql/mysql-error.log
log_queries_not_using_indexes  = 1
slow_query_log                 = 1
slow_query_log_file            = /var/log/mysql/mysql-slow.log

Here are status variables about open tables:

Open_tables 5,523
Opened_tables   14 k
Table_locks_immediate   6,792 M
Table_locks_waited  6,171 k

As you are saying that most of the tables are MyISAM, I would check IOs and locking.

You can verify you disk throughput with a tool like iotop.

http://www.manpagez.com/man/1/iotop/

MyISAM relies on the file system cache to store data blocks, so MySQL has to compete with all the other processes running on your OS for access to that cache.
The key_buffer only applies to index blocks.

http://dev.mysql.com/doc/refman/5.0/en/myisam-key-cache.html

If you notice that MySQL is generating a lot of IO activity, that may mean that your OS is starved for RAM in spite of the 16GB you say is available.

I ran into a similar problem with a Wordpress install lately that relied heavily on two 2GB MySIAM tables.

If your SELECT statements are trying to access data from these same tables that are being modified at the same time by (DML) INSERT, UPDATE or DELETE queries, that might explain why table_locks_waited is so high on your system. Those DML queries could be coming from a website, a cron job or some other process. If locking contention is the problem, you may want to consider converting the table engine to InnoDB for those tables that are causing the most locking. Tools like Jet Profiler and Innotop can help you to get that specific information.


SQL Server lock_escalation flag on ALTER TABLE -- can I really restructure without a table lock? SQL Server 2014 Standard Edition

In my situation, I have a huge table (100 million+ rows), and need to:

add column to it
set default value in that column

I see in
https://technet.microsoft.com/en-us/library/ms190273(v=sql.110).aspx
that there is a new lock_escalation option.

Does this allow me to, for example, do the following without a table lock:

e.g.

ALTER TABLE Protocols set (LOCK_ESCALATION = DISABLE);
go

ALTER TABLE Protocols
ADD ProtocolTypeID int NOT NULL DEFAULT(1)
GO

However, the concerns are:

a) LOCK_ESCALATION = DISABLE -- what exactly is it for, does it apply here? (The SQL Server docs are a bit thin... perhaps it only applies to partitioned tables?)

b) Does LOCK_ESCALATION = DISABLE affect the transaction size? (Appears it will still create one huge transaction?)

c) Will LOCK_ESCALATION = DISABLE even apply and avoid a table lock?

d) Is there a way to chunk it? (Can't imagine, but there is so much new magic in newer SQL versions... I should ask!)

You say the documentation is thin, but the answers are all there.
Lock Escalation
You really need to read that whole page, but essentially lock escalation only applies to 'regular' locks (e.g. shared, exclusive). Operations that change the structure of an object take much more restrictive schema locks (e.g. Sch-M) which prevent all concurrent access to the object being modified. As such, less restrictive 'ordinary' locks are not required. Ultimately, the question you ask is moot, because ALTER TABLE does not take row or page level locks that might be escalated to a partition or object level lock.
Regarding ALTER_TABLE (in the Remarks section):

Locks and ALTER TABLE
The changes specified in ALTER TABLE are implemented immediately. If the changes require modifications of the rows in the table, ALTER TABLE updates the rows. ALTER TABLE acquires a schema modify (SCH-M) lock on the table to make sure that no other connections reference even the metadata for the table during the change, except online index operations that require a very short SCH-M lock at the end. In an ALTER TABLE…SWITCH operation, the lock is acquired on both the source and target tables. The modifications made to the table are logged and fully recoverable. Changes that affect all the rows in very large tables, such as dropping a column or, on some editions of SQL Server, adding a NOT NULL column with a default value, can take a long time to complete and generate many log records. These ALTER TABLE statements should be executed with the same care as any INSERT, UPDATE, or DELETE statement that affects many rows.

and:

Adding NOT NULL Columns as an Online Operation
Starting with SQL Server 2012 Enterprise Edition, adding a NOT NULL column with a default value is an online operation when the default value is a runtime constant. This means that the operation is completed almost instantaneously regardless of the number of rows in the table. This is because the existing rows in the table are not updated during the operation; instead, the default value is stored only in the metadata of the table and the value is looked up as needed in queries that access these rows. This behavior is automatic; no additional syntax is required to implement the online operation beyond the ADD COLUMN syntax.

If you were using Enterprise Edition, the change you propose could be an instant, metadata only change (though any subsequent changes to data in the new column would require real allocations, data movement, and logging, of course).
As it is, adding this column will be an expensive, long-running, fully-logged, single transaction, size-of-data operation. There is no way to 'chunk' this change without changing the definition of the column (a nullable column can be added as a metadata-only change, then updated in chunks).


Which one is better performance and (approach): store as rows or as a varchar column OptionA:

TableA:

TableA_ID (PK,int)
..

TableB:

TableB_ID (PK,int)
..

TableC:

TableC_ID (PK,int)
..

TableD:

TableA_ID (FK, int)
TableB_ID (FK, int)
TableC_ID (FK, int) (Nullable)

Composite Key on (TableA_ID,TableB_ID,TableC_ID)

I am thinking to design TableD as the following (OptionB)

TableD:

TableA_ID (FK, int)
TableB_ID (FK, int)
TableC_ID (varchar(max) ) (Nullable)
and remove the composite key.
So, TableC_ID column will have something like these:
{1,2,3,4,....}

My application will need to use TableD as search a value in TableB_ID and/or TableC_ID column by giving the key for value in TableA_ID.

I am just wondering which option will be better performance.

Option B is the worst choice: Why
Because by keeping values in CSV format in a single column, you are violating first NF http://en.wikipedia.org/wiki/First_normal_form.

Most critical drawback of violation of first NF is complex join conditions. You have to apply join using LIKE operators and worst wildcard options or using other string functions.

Secondly RDBMS will not be able to create correct data distribution statistics and ultimately query optimizer can't select efficient execution plan.


ORA-00823: Specified value of sga_target greater than sga_max_size dba I was doing a modification in my database and something I should not have done well and I got the following error :

SQL> startup
ORA-00823: Specified value of sga_target greater than sga_max_size
ORA-01078: failure in processing system parameters

How to solve it ?

I solved it as follows:

Open the pfile and set sga_target=0 and save it.
After :

create spfile from pfile;
create pfile from spfile;

Now start the database :

SQL> startup

ORACLE instance started.
Total System Global Area 5133828096 bytes
Fixed Size                  8929496 bytes
Variable Size             956305192 bytes
Database Buffers         4160749568 bytes
Redo Buffers                7843840 bytes
Database mounted.
Database opened.


Creating two databases in Oracle 11g I am a student. I have assignments and projects simultaneously.
I want my project database and the other simple databases separate.
I have no option to use MySQL or others as my lecturer suggested to use only Oracle 11g and SQLPlus.
Could you please outline the process?

I'm a student and i have assignments and projects simultaneously. So I just want my project database and the other simple databases separate.

Kudos to you for some excellent thinking.
I know of "experienced", "professional" Developers that wouldn't have even thought of this.

... I have no option to use MySQL or others as my lecturer suggested only to use Oracle 11G and SQLPlus

Take this idea to your lecturer.
With any luck, they'll be as impressed as I was.
That said, Oracle 11 isn't SQL Server and creating an Oracle database is not a trivial exercise.  It's a shame you don't have access to Oracle 12, where Pluggable Databases make life a lot easier, but if you're stuck with '11 ...
I would suggest you use schemas within a single database to achieve your goal.
Here's how I would set up [one of] your Project[s]:
A Tablespace can be restored independently of any other so let's put all the "Project" stuff into a Tablespace of its own:
create tablespace my_project
   datafile '/.../my_project01.dbf' size 50M autoextend ;

A schema is just another account.
create user my_project
   identified by a_really_strong_password
   default tablespace my_project
   ;
grant create session, resource to my_project ;

Obviously to change the objects under the my_project schema, you'd sign on as the schema owner account.  However, to just use those objects, you probably ought to put a "safe distance" between you and doing any "damage".
Taking this step "away" from your data is another, big step to working well with Databases.
create role my_project_role
   identified by a_proper_password ;

The role will give [update] access to the objects in the schema, but only once you've given those privileges to the Role!  You'll need lots of the following, especially after you add, say, any new tables.
grant delete, insert, select, update on my_project.table_1 to my_project_role ;

If you're going to have other people looking at this, you might also want a second role to allow this, but not to let them break anything:
create role my_project_read
   identified by another_proper_password
   ;
grant select on my_project.table_1 to my_project_read ;

Lastly, you need an account for yourself:
create user me
   identified by yet_another_proper_password
   ;

grant create session, my_project_role to me ;

alter user me default role none ;

Now, when you sign on, you'll need to activate the role, with its password, before you can see any of your my_project.* objects.
set role my_project_role identified by a_proper_password ;

OK, I grant you this is a bit inconvenient, but it is more secure.
That said, if you're anything like me, after about a week you'll be royally fed up with it and you'll want the role to just be there, available "by default".
To achieve this, don't have a password on it and make it [one of] your "default" role[s]:
alter role my_project_role NOT IDENTIFIED ;
alter user me default role all ;


How can i give SECADM to instance owner in DB2? i'm using db2 9.7 on linux and created a user "db2fee1i" to be a instance owner. I restored a database, but even being SYSADM i only can connect to database. I need to give DATAACCESS to another user, and even doing a query appears...

  The command was processed as an SQL statement because it was not a
  valid Command Line Processor command. During SQL processing it
  returned: SQL0551N "DB2FEE1I" does not have the required authorization
  or privilege to perform operation "EXECUTE" on object
  "NULLID.SQLC2H23". SQLSTATE=42501

I tryed everything but nothing solves my problem, now i found "SECADM" that can give me the "privileges to execute" into database, but i dont know how to do it. Someone can help me?!

Under 9.7, whichever ID creates the database is automatically granted DBADM WITH DATAACCESS WITH ACCESSCTRL and SECADM.

So you should be able to log in to the databases with that ID to perform the grants you need. If this came from another environment, you may have to create the ID locally in order to get into the database. Only an ID with SECADM can grant SECADM to another ID. And also with 9.7, you cannot GRANT to or REVOKE from yourself.

Now...as to your problem.....when you do not have EXECUTE on a NULLID package, then you may either need to perform a BIND of the appropriate bind file, or grant EXECUTE on that package. If it is a system package that pretty much everyone can use, you can always GRANT EXECUTE ON PACKAGE NULLID.SQLC2H23 TO PUBLIC.


Oracle: Why is DBW terminating my Instance? I was performing routine administrative tasks on an Oracle instance and I was not able to connect to it.

I proceeded to do as follows to reach a diagnosis:

I checked for Oracle processes in memory and saw that the instance was down.
I tried to startup the instance but it never responded to the startup command. It simply stalled. No output or feedback, no matter how long I wait. Only a kill -9 can get me out of it.
I reviewed the alert log and the last message was 3 days ago:

"DBW0: terminating instance due to error 472
Instance terminated by DBW0, pid = 14952"

Questions

What can cause BDW to terminate an instance?
Why I don't get any feedback, whether in the sqlplus console or the alert log when I try to startup the instance.

When I ran strace, I got this:

ERROR: unable to open /dev/log.

I'm running Oracle on SunOS 5.8 Generic_117350-08 sun4u sparc SUNW,Sun-Fire. The RDBMS version is 9.2.0.8.0

EDIT:

I followed advice from both @balazs-papp and @jsapkota about running truss:

When I run truss on the sqlplus the I try to startup the instance, I get this output:

read(0, " s t a r t u p\n", 1024)               = 8
write(9, "\0 U\0\006\0\0\0\0\0038A".., 85)      = 85
read(10, 0x10029A536, 2064)     (sleeping...)
signotifywait()                 (sleeping...)
lwp_cond_wait(0xFFFFFFFF7D62B058, 0xFFFFFFFF7D62B068, 0xFFFFFFFF7D621C80) (sleeping...)
lwp_cond_wait(0xFFFFFFFF7D62B058, 0xFFFFFFFF7D62B068, 0xFFFFFFFF7D621C80) (sleeping...)
door_return(0x00000000, 0, 0x00000000, 0) (sleeping...)

How can I interpret this output? It's totally cryptic to me.

I exected startup nomount with explicit pfile paremeter and it works.

I have the init < SID >.ora in the $ORACLE_HOME/dbs/  directory, and always was working.


Are postgres connection able to manage multiple requests at the same time? I have an application using node Postgres driver to connect to my Postgres 12.
The pool size is currently 1.
Is it possible, for one connection, to have multiple requests sent before receiving the responses?
Figure two requests:

request A will last 10 seconds to be processed by the database,
request B will last 1 second.

If this is possible, that request B will be answered before request A?

No, you can only have a single statement executing at the same time on a PostgreSQL connections.
PostgreSQL v14 introduces pipelining of SQL statements, but that still means that one statement will execute after the other. Besides, I doubt that Npgsql already has support for that.
Use several database connections.


Trace Flag impact to Mirrored Databases I want to enable dead lock trace flag -t1222 on a production SQL Server 2014 instance. Many databases on this instance are configured with database mirroring. What (if any) impact to mirrored databases will occur after applying -t1222 and restarting the server?

What (if any) impact to mirrored databases will occur after applying -t1222 and restarting the server?

Since 1222 writes deadlock information to the log, the impact to mirroring itself will be nothing. Secondary effects should also be intangible in a normal environment. I'd only be worried if, for some reason, you have so many deadlocks that the Errorlog ends up filling your drive as this is fairly benign.

Also, by default, the extended events session does track deadlock information but it may not have all of the deadlock graph given the size of the deadlock graph.


The specified network name is no longer available We have an application that access the database (Sql server 2014 enterprise edition). The application calls a stored procedures to access the database. Everything was working fine until recently start sending the following error and stop the applications. Restarting the app fix the issue temporarily but facing the same error latter.

  Error: A transport-level error has occurred when receiving results from the server. (Provider: TCP Provider, error: 0 - The specified network name is no longer available.)

I did a lot of research which most of them pointing out as a network issue but could not find anything to actually solve the problem.  Do anyone know what change I should have to make on the database side to resolve this issue.  I highly appreciate any suggestions.

Sounds like spotty DNS resolution issues or long running queries.
For DNS trouble-shooting
As a Band-Aid and trouble-shooting aid add and entry to the hosts file on your app servers (not the SQL Server) at c:\windows\system32\drivers\etc
SQLServerIPAddress SQLServerName

Example:
172.16.0.5 ProductionSQLBox

That way, the SQL Server name will be resolved by the hosts file until you can find the real problem for what's going on with name resolution.
Be sure and test the hosts file by pinging the SQL box from the app server via a command line. Or, alternately create a DSN with the ODBC Administrator within Control Panel and test the connection there.
For long running queries
Implement a longer command timeout value for the application connection strings connecting to the SQL Server.
Example
<connectionStrings>
    <add name="webconfigconnectionString" connectionString="server=SQLServerName;database=dbName;uid=userName;password=ABC123;Connect Timeout=120" />
</connectionStrings>


What is the best way to store user profiles and logins? I need to store user profiles and logins. Is the best way to do that to have a table for logins (email, username, and password) and a separate table for their profile/info (e.g. their progress in something)? I am using MySQL if that makes a difference.

In general, if tables relate one-to-one, put both sets of columns in a single table.  (There are exceptions, but I don't see one here.)

On the other hand, "progress" sounds like some type of log?  That is, there might be lots of rows for each user?  Then you must have 2 tables, one with user info, one with 'progress'.

The user table would have a user_id as the PRIMARY KEY and the log table would have an INDEX(user_id, datetime) or something like that.  (I am assuming there is a datetime/timestamp column in the log.)


dm_db_index_physical_stats results inconsistent with actual object properties - what may be the reasons? SQL Server 2008 RTM (10.0.1600.22)

Running a typical index fragmentation report on a table:

SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName,
       i.name AS IndexName,
       s.index_type_desc,
       s.avg_fragmentation_in_percent,
       s.fragment_count,
       s.page_count,
       s.record_count
FROM
    sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('RPT_XXX'), NULL, NULL, 'DETAILED') s
    INNER JOIN
    sys.indexes i ON i.OBJECT_ID = s.OBJECT_ID
                       AND i.index_id = s.index_id
                       and i.object_id = OBJECT_ID('RPT_XXX')
WHERE
    s.avg_fragmentation_in_percent > 25

tells me that one index on that table has 7 fragments, 7 pages, about 700 rows and over 28% fragmentation. However, when I look at the index storage properties in the Management Studio, I get nearly 9000 pages, over 12 million rows, and 0.01% fragmentation, which are the expected numbers because the table is big and I just did the index rebuild and updated statistics on the table.

What, apart from a possible bug in the unsupported RTM version of SQL Server, might be the reasons for this discrepancy?

Before I gave anyone else a chance to answer this, I found the problem with my query.

dm_db_index_physical_stats(), when running in the 'DETAILED' mode, returns one row for each index level. In my case the leaf level of the index indeed has 0.01% fragmentation and is therefore filtered out by the s.avg_fragmentation_in_percent > 25 criteria, leaving only one intermediate level in the result.

Arguably you would only want to look at the leaf level of the index to identify fragmentation, so the query should look like so:

SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName,
       i.name AS IndexName,
       s.index_type_desc,
       s.avg_fragmentation_in_percent,
       s.fragment_count,
       s.page_count,
       s.record_count
FROM
    sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('RPT_XXX'), NULL, NULL, 'DETAILED') s
    INNER JOIN
    sys.indexes i ON i.OBJECT_ID = s.OBJECT_ID
                       AND i.index_id = s.index_id
                       AND i.object_id = OBJECT_ID('RPT_XXX')
WHERE
    s.index_level = 0 AND           -- leaf level only
    i.is_hypothetical = N'0' AND    -- exclude hypothetical indexes
    s.page_count > 1000 AND         -- look at large indexes only
    s.avg_fragmentation_in_percent > 25

The criteria i.is_hypothetical = N'0' comes from observing the query generated by the Management Studio in the trace, as suggested by Aaron Bertrand.

Edit: It may also make sense to exclude smaller indexes from this analysis, because their fragmentation a) may not be important and b) may not be significantly improved.


Coping MySQL DB between two servers: which server shoud initiate the operation? I want to copy a database from serverA to serverB. The servers can communicate directly with each other. So I'm able to do something like:

ServerA is sending the database to ServerB:

dbadm1@serverA:~$ mysqldump -C ... database | mysql -h serverB ... database

or: ServerB is ,,draining'' the database from ServerA:

dbadm2@serverB:~$ mysqldump -C -h serverA ... database | mysql ... database

The result of both commands will be, of course, the same.

But which one is better/faster/etc.? Or are they the same?
Initiate coping from serverA or from serverB?

I think the performance difference will be minor.  Do what is more convenient.

If one machine is more powerful, run it on that machine -- Note that you will have 3 processes (mysqldump, mysql, mysqld) on that machine and 1 (mysqld) on the other.


How to add PK on every id column in Postgres I have an issue. I have database backup for existing DB let's call it dev_db. I recovered it but under a different name like master_db and backup how I understood has only tables, columns types and etc but it hasn't system information and tables like pg_constrains table and etc that's why I lost all my PK.

So, my first question can I update all tables in my database with PK for "id" column, is it enough? (if yes how to perform that)
And my second question is how can I create a full backup of the necessary database with all system information?

You can generate the SQL to add the primary key with:

select concat('ALTER TABLE ',table_schema,'.',table_name,' ADD CONSTRAINT ',
              table_schema,'.pk_',table_name,' PRIMARY KEY (id)')
from   information_schema.columns
where  column_name ='id'
and    table_schema='xxx';

Replace the xxx with the schema for which you want to create the primary keys. You only need to write the output to a file and check and execute it.


Using Postgres to return a selection based on frequency distribution I am not even sure if this is a possibility so my apologies if I'm barking up the wrong tree; I am ignorant of Postgres's limitations as I am self-taught.
This is hypothetical, but if I had a table like so:
dice_rolls

roll_1
roll_2
sum

1
1
2

1
2
3

1
3
4

1
4
5

1
5
6

1
6
7

2
1
3

2
2
4

2
3
5

2
4
6

2
5
7

2
6
8

3
1
4

3
2
5

3
3
6

3
4
7

3
5
8

3
6
9

4
1
5

4
2
6

4
3
7

4
4
8

4
5
9

4
6
10

5
1
6

5
2
7

5
3
8

5
4
9

5
5
10

5
6
11

6
1
7

6
2
8

6
3
9

6
4
10

6
5
11

6
6
12

we can see that the value '7' for sum appears most frequently. If we plotted the frequency distribution of the sum, it would look something like this:
               x
            x  x  x
         x  x  x  x  x
      x  x  x  x  x  x  x
   x  x  x  x  x  x  x  x  x
x  x  x  x  x  x  x  x  x  x  x

2  3  4  5  6  7  8  9  10 11 12

Therefore, we can assume that a truly random selection of this dataset will follow this distribution pattern. What I'm interested in is psuedo-random DISTINCT selection of N rows that attempts to normalize the distribution based on the sum column.
E.g.,
SELECT * FROM dice_rolls ORDER BY RANDOM() LIMIT 5;

is what I have now. This query is most likely to contain rows where the sum is 5, 6, 7, 8, 9. How would I go about using a custom ORDER BY function that would be significantly more likely (or even strictly) to, return rows where the sum has an even chance of being 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, or 12?

Get a CTE with the different sums, pick one and return a matching row:
WITH sums AS (
   SELECT DISTINCT dice_sum FROM dice_rolls
)
SELECT * FROM dice_rolls
WHERE dice_sum = (SELECT dice_sum FROM sums ORDER BY random() LIMIT 1)
ORDER BY random() LIMIT 1;


MySQL cannot create foreign key, errors 1005, 150 I'm trying to add a foreign key to a MySQL InnoDB table, linking a BIGINT with a table within the same database. I'm using MySQL Workbench to do this. It generates a script:

ALTER TABLE `MyDatabase`.`MyTable`
  ADD CONSTRAINT `myNewForeignKey`
  FOREIGN KEY (`TheId` )
  REFERENCES `MyDatabase`.`MyReferencedTable` (`id` )
  ON DELETE NO ACTION
  ON UPDATE NO ACTION
, ADD INDEX `myNewForeignKey_idx` (`TheId` ASC) ;

When applied I get:

ERROR 1005: Can't create table 'MyDatabase.#sql-1ab6_5c3df97' (errno: 150)
ERROR: Error when running failback script. Details follow.
ERROR 1050: Table 'MyReferencedTable' already exists

I even tried removing the table (could do that as it's empty) and recreating it with foreign keys and all, but it leads to the same errors.

I've seen reports of bugs relating to this, but they are several years old and should've been fixed by now. Surely foreign keys must be possible to add. What am I doing wrong?

I confused my databases and incorrectly thought I was working with InnoDB databases. Turns out one of them was MyISAM and then foreign keys don't work. Unfortunately, MySQL Workbench doesn't inform the user of this on Windows, but it does on the Macintosh version. (I guess it's good after all to have developers on your team with Macs.)

Hope this helps somebody with the same error message.


Hibernate modelling a N:N Relationship Lets say that we have two entities
Entities:

Bus        (idBus*)
Stop       (idStop*,stopDescription)

Where a Bus passes from many Stops and vice versa, therefore the cardinality is N:N.

My question arises from the fact that when we consider to know the idStop and the idBus then we will also know a number of attributes like Direction,NextStopID, TimeOfArrival
How should i map those attributes?

My thought is to make a bridge table having idStop and idBus as Foreign Keys and a number of extra attributes that are not present in either the Bus or the Stop table, but I don't know if this is doable or the correct way to go.

Any thoughts?

Yes, a bridge table (also known as junction table, mapping table etc.) is a perfectly sensible solution in this case, and it is common for a bridge table to have other attributes beside the references to the tables being connected.

As a stationary object, a stop is just a place, which has coordinates and may also have a fixed name as well as some other properties. As a part of a route, though, it can be assigned the additional attributes you have mentioned that would depend on the route, i.e. on the specific bus number. Those would need to be stored in a different table, the one that would link the stop and the route.

So, if by "bus" you mean "bus number" (or "bus route", "bus line"), then by all means create a dedicated table with references to Bus and Stop and with whatever additional attributes you deem necessary. Note, though, that if Bus is meant to store information about specific buses (vehicles), then I would consider creating another entity (called Route perhaps) that I would link to in the bridge table. Bus would then probably reference that new entity, either directly or, again, through another bridge table (in case vehicles were allowed to work alternately on different lines).


indexable JSON datatype in MySQL/MariaDB In PostgreSQL 9.4 there is a JSONB datatype which allows you to index on fields in the stored JSON objects. Does MySQL/MariaDB have an equivalent?

I'm specifically interested in the MariaDB Galera cluster, but am open to any MySQL-family answers.

You can do this in MySql 5.7. What you are looking for is adding an index over Generated Columns. You can find an example here:
http://mysqlserverteam.com/inline-json-path-expressions-in-mysql-5-7/


Dropping empty partitions in Oracle 11g DB I have task to drop empty (0 rows) partitions and subpartitions from data warehouse.

I'm preparing script that:

Will count the rows of partition/subpartition
When row count will be 0 the partition should be dropped (and confirmation message)
When row count is different than 0, message that this partition
was not empty

To check number of rows I created SELECT 'partition_name', COUNT(*) FROM user.table PARTITION (partition_name);
I was advised that count will be better way than basing on NUM_ROWS column of DBA_TAB_PARTITIONS or DBA_TAB_SUBPARTITIONS (however from those 2 I will extract the list of potential candidates with other data to excel for later analysis).
I was also advised not to use dbms.output.

There are millions of potential candidates (10M+ on some dbs).On analysis stage I plan to exclude 'MAXVALUE' partitions and HASH type using Excel filters and then use above script.

Does someone have similar script? I need pattern for one partition and then I will generate rest in Excel.
Are there more things to exclude from drop list on analysis stage?

I am using a procedure like this:

PROCEDURE MaintainPartitions IS

    CURSOR PartTables IS
    SELECT TABLE_NAME, PARTITION_NAME, HIGH_VALUE
    FROM USER_TAB_PARTITIONS
    WHERE TABLE_NAME LIKE ....
    ORDER BY TABLE_NAME, PARTITION_POSITION;

    cur SYS_REFCURSOR;
    r INTEGER;
    highValue VARCHAR2(100);

BEGIN
   FOR aTab IN PartTables LOOP
      EXECUTE IMMEDIATE 'BEGIN :ret := '||aTab.HIGH_VALUE||'; END;' USING OUT highValue;
      IF highValue NOT IN ('DEFAULT', 'MAXVALUE') THEN
         OPEN cur FOR 'SELECT ROWNUM FROM '||aTab.TABLE_NAME||' PARTITION ('||aTab.PARTITION_NAME||') WHERE ROWNUM <= 1';
         FETCH cur INTO r;
         IF cur%NOTFOUND THEN
            EXECUTE IMMEDIATE 'ALTER TABLE '||aTab.TABLE_NAME||' DROP PARTITION '||aTab.PARTITION_NAME||' UPDATE INDEXES';
         END IF;
         CLOSE cur;
      END IF;
   END LOOP;
END;

Note, you cannot drop a subpartition. In such case you have to use TRUNC.

Another note, in case of INTERVAL partitions you may face ORA-14758: Last partition … cannot be dropped. Have  a look at https://stackoverflow.com/questions/1395379/unable-to-delete-oldest-table-partition to manage it.


Converting standard_conforming_strings from off to on when importing Postgres dump I've got a Postgres 8.1 instance which has standard_conforming_strings set to off (== handles slashes as escape characters). Recent Postgres versions have this setting on by default (for good reasons).

When importing a dump from 8.1 to 9.x, then I can set this setting to off in the 9.x DB but I don't want to, I'd prefer converting the dump into the proper format. This is a ~100GB dump.

How could I do that? Can I simply turn this setting 'on' in the 8.1 version? (Will that affect exporting existing data? Will/can that corrupt or modify existing data?)

What doesn't work: simply importing data produced by 8.1 to 9.x without setting standard_conforming_strings to off in the 9.x DB; the result is corrupted data.

Testing with a 8.1 instance, it doesn't look that there is a problem. See these 3 different examples:

Create data

 SHOW standard_conforming_strings ;
 standard_conforming_strings
 -----------------------------
  off

CREATE TABLE bla(t text);
INSERT INTO bla VALUES('abc''def');
INSERT INTO bla VALUES(E'abc\\def');

1. Dump of 8.1 data with 8.1 pg_dump

$ pg81/bin/pg_dump db

this produces (after removing the irrelevant stuff):

COPY bla (t) FROM stdin;
abc'def
abc\\def
\.

Here standard_conforming_strings does not matter because the COPY format is independant from it. When this COPY is executed, it will make no difference whether it's on or off.

2. Dump of 8.1 data with 8.1 pg_dump with --inserts option

$ pg81/bin/pg_dump --inserts db

So COPY is not used.
The dump now contains, for the interesting part:

INSERT INTO bla VALUES ('abc''def');
INSERT INTO bla VALUES (E'abc\\def');

Since the E'...' syntax is used, standard_conforming_strings is again irrelevant. These strings will be interpreted correctly,  whether the setting is on or off, by any version of postgres>=8.1

3.Dump of 8.1 data with 9.1 pg_dump with --inserts option

$ pg91/bin/pg_dump --inserts db

Now the dump happens to contain this, somewhere at the start

SET standard_conforming_strings = off;

and then in the data section:

INSERT INTO bla VALUES ('abc''def');
INSERT INTO bla VALUES ('abc\\def');

So again it's being taken care of automatically, albeit differently than with 8.1's pg_dump, but this dump is going to be reloadable directly in any version of postgres>=8.1, without tweaking anything.


MySQL: Replicate data within 2 same schemas on 1 mysql server This might sound a bit crazy, please let me explain the reasoning behind this:

we plan to use a real master-slave replication for production
we want to use something small'n easy for local development

We mostly have Java/PHP devs who should be able to inspect state changes of master/slave databases. The easiest (infrastructure-wise) way seems to be just have a one MySQL server with 2 same schemas (so master/slave is not server/server, but schema/schema instead).

Is this easily achievable with MySQL or do we have to install 2 MySQL servers even for local testing?

You could setup a script to read the binary log, convert it to SQL (with the utility mysqlbinlog) and change the schema on-the-fly before applying the changes. You could also setup triggers on every table to INSERT/UPDATE/DELETE every row on the copy schema. But that is a lot of work to do it right.

Running 2 instances of MySQL on the same machine is very easy, and may be closer the the production deployment. Tools like MySQL sandbox makes running "n" different instances of MySQL even easier, taking just a command to setup an instance or a complex replication topology! You can even simulate replication lag with MASTER_DELAY or pt-slave-delay to see how your application behaves when the state of both schemas is not the same.


Control InnoDB buffer pool allocation in MySQL (5.7+) I have some large tables which causes to fill the InnoDB buffer pool and removing other data.

Is there any way how can I control the InnoDB buffer allocation in one shared database?

Like:
a) Limit the buffer size for one table
b) Limit the buffer size for data (so indexes will remain cached)
c) Allocate a buffer exclusively for one particular table or even for one particular table index (or for all indexes in one table)

I am sorry to bring this up but only MyISAM does what you asking for when it comes to caching indexes for a specific table. I have written about this before. At this time, using MyISAM is like MySQL sacrilege, but if you are that desparate to cache indexes independently and your tables are read only, you can try converting them to MyISAM and setting up dedicated keycaches. CAVEAT : YOU DIDN'T HEAR THIS FROM ME !!
BACK TO REALITY...
Perhaps you should consider taking snapshots of the Buffer Pool.
Whenever you get the InnoDB Buffer Pool into the state you want, run this
SET GLOBAL innodb_buffer_pool_dump_now = 1;

This will generate a file called ib_buffer_pool (usually located in datadir), containing a map of all tablespace_ids that are in the Buffer Pool at that moment. You could copy this file to some folder and retrieve it later.
You could reload ib_buffer_pool by running
SET GLOBAL innodb_buffer_pool_load_now = 1;

This will copy all .ibd pages (data and indexes) back into the InnoDB Buffer Pool.
You should consider taking a snapshot of the buffer pool before running a backup and reloading the buffer pool afterwards.
For more information, please read the MySQL Documentation on innodb_buffer_pool_dump_now and innodb_buffer_pool_load_now.


Moved MySQL datadir Now Server Will Not Run! My problem yesterday (MySQL - Alter Table Fails As Workbench Tries To Create Existing Table)  was caused by /dev/sda2 (/root) running out of space.

The cause of this was that, by default, MySQL had installed the DataDirectory (datadir) on /dev/sda2(/root).
I therefore recursively copied its contents to /dev/sda3 (/home) after stopping the MySQL server.
I also editted /etc/init.d/mysql, /etc/mysql/my.cnf and /etc/apparmor.d/usr.bin.mysqld to reflect this change.

After both trying to restart the MySQL server and a reboot the server is still not running.  The error.log in /var/log/mysql is also not being appended to. Obviously I have missed something but what? I am running Linux Mint 13.

I have done the same thing. Be absolutely sure that all rights and owners are intact. You can do this with cp -rp if I'm correct. Rsync the folder locally should also keep owners and rights intact. You don't have to change the config however. A symlink will do.

Rename the original datadir to datadir-old or something similar.
Then create a symlink to the new location.
If you want the symlink to have right owner, create a new folder (tmp), move the symlink inside that folder, then change the owner of the tmp folder and its contents, then move the symlink back.
Undo the changes to the config files.

On the commandline... (I haven't tried this so check if all is as it should be!)

sudo -i
cd /var/lib
mv mysql mysql-old
ln -s /home/mysql mysql
mkdir tempdir
mv mysql tempdir
chown -R mysql:mysql tempdir
mv tempdir/mysql .
rm -rf tempdir

In the directory listing I see the following on my system. Yours should be similar!

drwx------  7 mysql         mysql          4096 Feb  6 12:01 mysql

-rw-rw----  1 mysql mysql 1671430144 Feb  6 17:37 ibdata1
-rw-rw----  1 mysql mysql    5242880 Feb  6 17:37 ib_logfile0
-rw-rw----  1 mysql mysql    5242880 Jan 28 14:35 ib_logfile1
drwx------  2 mysql mysql      24576 Jan 28 14:34 testdatabase_local
drwx------  2 mysql mysql       4096 Jan 28 13:38 mysql
-rw-rw----  1 mysql mysql          6 Jan 28 13:38 mysql_upgrade_info
drwx------  2 mysql mysql       4096 Jan 28 13:38 performance_schema

Try this and see if that fixes your problem.


How to select rate for all selected modules and all selected years no matter they got rate for that year or not How to write a SQL to achieve the "goal" section?  Select rate for all selected modules and selected years no matter they got rate for that year or not.

The years and modules will be input by users, then the SQL will return the sum rate for selected years and selected modules.

For instance, Find rate for ASD,EB,GOF modules from 2008 till 2012, as the above

WITH finanimpact
     AS (SELECT Extract(YEAR FROM fiwm.create_dt) AS years,
                m.code                            AS mcode,
                SUM(fiwm.rate)                    AS rate
         FROM   master.financial_impact_wfins_mapping fiwm
                inner join master.wf_instance wf
                  ON wf.sysid = fiwm.wf_instance
                     AND wf.template_id = 4
                inner join master.wf_data_cr wdc
                  ON wdc.instance_id = fiwm.wf_instance
                inner join parameter.module m
                  ON m.sysid = wdc.module
                     AND m.sysid IN ( 4, 15, 6 )
         GROUP  BY Extract(YEAR FROM fiwm.create_dt),
                   m.code
         UNION
         (SELECT NULL,
                 m1.code,
                 0
          FROM   parameter.module m1
          WHERE  m1.sysid IN ( 4, 15, 6 )
          MINUS
          SELECT NULL,
                 m.code AS mcode,
                 0
          FROM   master.financial_impact_wfins_mapping fiwm
                 inner join master.wf_instance wf
                   ON wf.sysid = fiwm.wf_instance
                      AND wf.template_id = 4
                 inner join master.wf_data_cr wdc
                   ON wdc.instance_id = fiwm.wf_instance
                 inner join parameter.module m
                   ON m.sysid = wdc.module
                      AND m.sysid IN ( 4, 15, 6 )
          GROUP  BY Extract(YEAR FROM fiwm.create_dt),
                    m.code)) SELECT fi.years,
       fi.mcode,
       fi.rate
FROM   finanimpact fi
UNION
(SELECT LEVEL + 2008 - 1,
        '',
        0 AS id
 FROM   dual
 CONNECT BY LEVEL <= 2012 - 2008 + 1
 MINUS
 (SELECT DISTINCT lfi.years,
                  '',
                  0
  FROM   finanimpact lfi))

returned result:
=========================
2008    null    0
2009    null    0
2010    EB  34640
2010    GOF 8660
2011    EB  103920
2011    GOF 12990
2012    null    0
null    ASD 0
=========================

goal:
=========================
2008    ASD     0
2009    ASD     0
2010    EB  34640
2010    GOF 8660
2011    EB  103920
2011    GOF 12990
2012    ASD 0
=========================

That looks like a homework question that is investigating how well you understand outer joins and maybe the NVL function (rather than the equijoins you currently have).


How can I organize my tables? My database contains more than 200 tables. When I want to modify or insert a table, I find it difficult to find it among all those tables.

Is there any way to collect related tables in a folder structure inside my DB? I'd have groups of folders, each one containing tables for a certain part of my application.
If that's not possible, how can I manage all these tables and make it easy for myself?

is there any way to collect tables related to certain part in a folder inside my DB?? ..so i have group of folders each one contain tables of certain part of my application

The direct answer to that within SSMS would be no.

However, there are two ways you can go about this:

Use schemas to contain logically connected and functional objects
Use a BroadToSpecific naming convention to alphabetically group objects (I.e. Person, PersonAddress, PersonAddressArchive, etc.  These will appear alphabetically grouped)


Cannot find Database Mail feature in SQL Server Express 2012 SP1 I need to use SQL Server Database Mail feature in my project. However Database Mail seems not available on my development instance.

There should be a 'Database Mail' node in the Object Explorer. In this screenshot I can't seem to find any. Also shown is my SQL Server version.

Is Database Mail not supported in this SQL Server version? Which SQL Server version (or edition) required to use Database Mail?

All documentation I can find online always assume the feature is already installed and just go on explaining how to configure it. If my SQL Server version supports Database Mail, how to install/enable the feature?

You can use SQL Mail with SQL Server Express edition.

Borrowing liberally from Sending Mail using SQL Server Express Edition

To configure SQL mail we need to follow below steps.

--Create Sysmail Account
--Use sysmail_add_account_sp stored procedure of MSDB database to configure sysmail  account.
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'MailTest',
@description = 'Sent Mail using MSDB',
@email_address = 'YourEmail@test.com',
@display_name = 'DisplayName',
--@username='umashankar@queryingsql.com',
--@password='password',
@mailserver_name = 'mail.server.com'

--Creating Database Profile
--Use sysmail_add_profile_sp stored procedure of MSDB database to configure Database Profile.
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'MailTest',
@description = 'Profile used to send mail'

--Add database Mail account to profile
--Use sysmail_add_profileaccount_sp stored procedure of MSDB database to map database mail account to Profile.
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'MailTest',
@account_name = 'MailTest',
@sequence_number = 1

--Grants permission for a database user or role to use a Database Mail profile.
--To Grant permission for a database user or role to use a Database Mail profile use sysmail_add_principalprofile_sp.
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'MailTest',
@principal_name = 'public',
@is_default = 1 ;

--Enable 'Database Mail XPs'.
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO

--Send Mail using Created Profile
exec msdb.dbo.sp_send_dbmail @profile_name = 'MailTest', @recipients = 'receiver@queryingsql.com', @subject = 'Mail Test', @body = 'Mail Sent Successfully', @body_format = 'text'

I will say that SQL Mail is deprecated and may be removed in a future version of SQL Server Express, but it still seems to work in the latest edition.


Create a T-sql script JOB SQL-2012 and scheduled I would like to create a T-sql script and scheduled. My goal is create all my databases (4 databases) and when the QA or DEV team need to test something, they can use these databases.
The problem is that I don´t have other server so I have to use the same server, for example I have a database called AUXDB, so I need to create a new database as PRE_AUXDB.
I have been creating all the scripts per each dabatase (just structure) but I need to know, how to create a header script to execute each script.
I hope you can understand my request.

Thank you in advance.
Raúl

Ok, after yor comment it is clear. I will do the following:

Create a new DB called SERVICE, MAINTANANCE, etc.
Inside it create a stored procedures for different steps - drop old DB, create new DB, populate new DB. You can make all that in one procedure also.
Create one procedure which will execute others - step by step (if you not implemented all logic in one procedure).
Creata a job with permissions - inside it strat main procedure. Or tell DEV team to use main procedure inside new DB (with appropriate permissions).


Can I avoid this cursor? Let's say I have two tables in a banking application and table names are all_transactions and helpline_requests. The 'all_transactions' table keeps track of all kinds of transactions including deposits, transfers, etc. The 'help_requests' table keeps track of the helpline calls a user has issued. For brevity the table schemas and sample data is given:

all_transactions
---------------
id, class, date, user_id
1, 'transfer', '2019-04-15 11:02:45', 789
31, 'transfer', '2019-04-14 05:02:45', 789

helpline_requests
-----------------
id, user_id, date
2, 789, '2019-04-14 11:02:45'

I need to see if a given customer has deposited and called helpline within a 24 hours timespan, more than once in last one week.

For example, user_id:789 has transferred money on June 19th morning and called helpline on the same day June 19th evening. This is one occurrence because it happened within a 24-hours timespan. If we detect any such occurrence once more in last 7-days, starting from his latest helpline request date, I need to return something indicating it, may be print something for now.

I have achieved this using a CURSOR approach and my code is below:

DECLARE @occurencesFound TINYINT
DECLARE @withReqId INT, @requestDateForLoop DATETIME
SELECT @occurencesFound = 0

-- A cursor for last 7 days data
DECLARE helpline_Cursor CURSOR FOR SELECT id, [date] FROM helpline_requests WHERE [user_id] = 789 and [date] >= DATEADD(day, -7, getdate()) order by id desc;
OPEN helpline_Cursor;
FETCH NEXT FROM helpline_Cursor INTO @withReqId, @requestDateForLoop;
WHILE @@FETCH_STATUS = 0 AND @occurencesFound < 2
    BEGIN
        SELECT @withReqId, @requestDateForLoop
        IF DATEDIFF(HOUR,
                        (select
                         top 1 t.[date]
                        from
                            [all_transactions] t
                        where
                            t.user_id = 789
                            and t.class = 'transfer'
                        order by
                            t.[date] desc),
                        @requestDateForLoop) <= 24
        BEGIN
            SELECT @occurencesFound = @occurencesFound + 1
            SELECT 'ok'
        END
        FETCH NEXT FROM withdraw_Cursor INTO @withReqId, @requestDateForLoop;
    END;
CLOSE helpline_Cursor;
DEALLOCATE helpline_Cursor;

IF @occurencesFound = 2
BEGIN
    SELECT 'This customer has transferred money and called helpline within 48 hours more than 1x a week'
END

Can I avoid cursor in this case?

My script may give minor syntax error or minor logic error because you haven't thrown sample data.

I am sure it cover your requirement,

--Write sql to get all user_id from helpline_requests as per your requirement
-- I am getting latest helpline date of each user
-- you can use your own logic
declare @Hr int=24
declare @CurDate datetime=Getdate()

declare @Period int= 7 --days
declare @FromDate datetime=datediff(day,-@Period,@CurDate)
;with CTE as
(
select [user_id],[date]
,ROW_NUMBER()over(partition by t.[user_id] order by t.[date] desc) rownum
from helpline_requests
where [date]>=@FromDate
)
,CTE1 as
(
select t.[user_id]
,ROW_NUMBER()over(partition by t.[user_id] order by t.[date] desc) rownum1
from all_transactions as T
cross apply(select [user_id] from CTE C
where c.user_id=t.user_id
and c.rownum=1
and datediff(hour,t.[date],c.[date])<=@Hr)ca
and t.[date]>=FromDate and t.[date]<=@CurDate
)
-- more than once in last 7 days
select * from CTE1 where rownum1>1


VMware Socket/Core allocation for SQL Server 2016 I am requesting a 16 vCPU 256GB RAM VM (Windows 2012|Host ESX 5.5) from our infrastructure team. However, my infrastructure team would like to know how the VM should be setup. What would be the ideal socket/core setup for this VM?

16 socket x 1 core |
2 socket x 8 core  |
4 socket x 4 core

Looking at this blog post it would seem 16 socket to 1 core may be the best setup: https://blogs.vmware.com/vsphere/2013/10/does-corespersocket-affect-performance.html

Can anyone offer and guidance or thoughts?

I found another blog post discussing this same question:

https://blogs.vmware.com/performance/2017/03/virtual-machine-vcpu-and-vnuma-rightsizing-rules-of-thumb.html

I'll likely need to ask for another VM for testing different configurations, but I think we're going to start with 2 Socket | 8 Core configuration.


Sequence and table with the same name? Can we have a sequence and table with the same name? I am trying to create a sequence with the same name as an existing table, but I get this error:

  There is already an object named 'XYZ' in the database.

I haven't found any documentation about this.

An attempt to document this is made here:

CREATE SEQUENCE (Transact-SQL)

  sequence_name
  Specifies the unique name by which the sequence is known in the
  database.

It is not very clear that the name must be unique across all objects in the database. And even that is not true. The name must be unique within the schema in a database.


Insert image with relative path to the postgres table I have created following table in postgres:

CREATE TABLE public.test
(
    id bigint NOT NULL DEFAULT nextval('test_id_seq'::regclass),
    file_content oid,
    name bigint NOT NULL,
    CONSTRAINT test_pkey PRIMARY KEY (id)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public.test
    OWNER to postgres;

To put some example data via sql script:

INSERT INTO public.test(id, file_content, name) VALUES (1, lo_import('test.jpg'), 14);

It works fine when I am putting full path to binary file, like:
C:\temp\myTest\test.jpg
However if I am putting just filename (sql script exists in the same directory) then I am getting error that file does not exists.

Do you know if it is possible to execute lo_import with relative path?

It uses PGDATA by default. We can see this using Process Monitor:

You'll have to explicitly set a path if you require otherwise.