Special Characters in MySQL using UTF8 Unicode Collation and LOAD DATA INFILE I have several csv files in am loading into MySQl using Java. In the Description field I have several Special Characters that are causing the load to fail. I am using LOAD DATA INFILE. This is nested in a for each loop which parses an array of filenames / tables and runs through each combination until it is finished with all the files.

Here is my jdbc connection string where I am passing a definitive collation param/value for UTF8 collation

 static String  url = "jdbc:mysql://localhost:3306/stage?verifyServerCertificate=false&amp;characterEncoding=UTF8";

Here is my LOAD DATA string.

final String sql2 = ("LOAD DATA INFILE" + filetoEat  +
       "INTO TABLE staging." +tableName +
       "CHARACTER SET UTF8
       FIELDS TERMINATED BY','  ENCLOSED BY '\"\'
       LINES TERMINATED BY '\n'  IGNORE 1 LINES");

Create Table Statement

CREATE TABLE `zmi064_02` (
  `Material` varchar(11) CHARACTER SET utf8 DEFAULT NULL,
  `Material Description` varchar(60) CHARACTER SET utf8 DEFAULT NULL,
  `MatType` text CHARACTER SET utf8,
  `MatGrp` text CHARACTER SET utf8,
  `ClassType` text CHARACTER SET utf8,
  `Class type Text` text CHARACTER SET utf8,
  `Class` text CHARACTER SET utf8,
  `Class Text` text CHARACTER SET utf8,
  `CharactName` text CHARACTER SET utf8,
  `CharactValue` text CHARACTER SET utf8,
  `Plant` varchar(6) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Show variables

character_set_client        utf8
character_set_connection    utf8
character_set_database      utf8
character_set_filesystem    binary
character_set_results       utf8
character_set_server        utf8
character_set_system        utf8
collation_connection        utf8_general_ci
collation_database          utf8_unicode_ci
collation_server            utf8_general_ci

The code is working fine until it comes across a special character like a degree symbol or micro symbol µ within a Material Description . At that point it throws an Exception

Invalid utf8 character string: 'LUG'

The string LUG is followed by a µ symbol. The DB is set to utf8 - utf8_unicode_ci and the column in question is a VARCHAR(60) that holds material descriptions. I have tried using  ESCAPED BY '\' but I can't seem to get it working correctly. I have also tried CHARACTER SET UTF8. I have also tried different collation ie, utf8_general_ci to no avail.

Any insight is greatly appreciated

I figured that I would answer this now that I found the solution. Because I am using Java to run the LOAD DATA INFILE via JDBC the JDBC driver seems to be checking the collation at the DB and not the actual table being loaded as it is parsing the file. So you can't have the DB set to UTF-8 and have a Latin collated table as you would be able to do with an INSERT statement. I had tried to set the Table collation as Latin and even had the field in question Latin, but until I changed the entire DB to Latin it was failing. The CSV files are large so checking every char in question is not easy, but I was catching the Exceptions in Java and was able to determine that the error was generated by the JDBC driver and was complaining that "Character at line xx is not a UTF-8 character" Running in Debug allowed me to see more details.

I then concluded it must not be looking at the Latin collated table it would be filling, but was looking at the DB which was still set to UTF-8. Changing the DB to Latin was all I needed to do.

I want to thank @Rick James for his help, I hope this will help others in the future.

Pat


Disable an option of Script Is it possible to disable the option to generate a database script from SSMS? From Right click on a DB >> Task >> Generate scripts..

Thanks,

There is no direct option to disable the generate scripts function. This option comes with the ability to VIEW DEFINTION. What level of access does the user have? What do they really need to do?


(MySQL) user table and profile separate, or same table? I'm creating a blogging site that anyone can sign up and be an author for. I want each author to have their own public profile. My main question is; would it be better practice to have a separate profile table that contains fields that would be specific to only the profile page, or should I just add those profile fields to a general users table as this example shows:

CREATE TABLE IF NOT EXISTS `users` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `username` varchar(255) NOT NULL,
 `first_name` varchar(255) NOT NULL,
 `last_name` varchar(255) NOT NULL,
 `email` varchar(255) NOT NULL,
 `password` varchar(32) NOT NULL,
 `sign_up_date` date NOT NULL,
 `activated` enum('0','1') NOT NULL,
 `admin` int(1) 0 ,
 `bio` text NOT NULL,
 `profile_pic` text NOT NULL,
 PRIMARY KEY (`id`)
)

bio and profile_pic are both profile fields in this case. This is also just an example, I plan to have more fields for general user stuff and not to many more for the public profile, but that could change as I move further into development.

Sorry if this an opinion and not a best practice question. I'm new to Database architecture.

Keep them together.  You're describing vertical partitioning, which is generally only used when a table is getting really wide - dozens or hundreds of fields.  Until and unless there's a compelling reason to separate them (e.g., a single blogger can have multiple profiles, for different blogs, audiences, or languages), there's no need to complicate the schema.


How to replicate schema changes from local database to a remote? I was working with some big database and I want to replicate local changes from a local database (I just added some columns to a few tables) to a remote database on other server.

The thing is that I can't backup data and create new schema and after that put in all data again cause the data is huge like 10.GB, I think there should be a better way of doing this.

So what is the properly way of doing this?

Note:
When I was doing changes at MS SQL Server Management Studio using the design view the IDE don't give me any sql log with changes.

Next time, click the Generate Scripts button and then execute the generated SQL script at both the local and remote databases.

For the changes you made locally, that you now want to replicate to the remote server, you'll need to either recreate them manually on the remote server or use a tool to identify the differences, such as RedGate's SQL Compare or Visual Studio's Schema Compare tool.

There are other methods, but I think these are likely going to be the easiest approach for you.

Finally, the "proper" way to handle changes like this is to tap into some release management methodologies.  This topic can get pretty complex depending on your needs, but a good primer can be found here along with a listing of some of the tools available on the market.


How to pivot a table skipping bad value in Postgres Given a table like:

 label |   x   |   y   |   z
-------+-------+-------+------
 AAA   | NULL  |   7   |   9
 BBB   | 0     |  10   |   0
 CCC   | 12    | NULL  |   0

create table coords (label text, x integer, y integer, z integer);
insert into coords(label,x,y,z)
values
    ('AAA',NULL,7,9),
    ('BBB',0,10,0),
    ('CCC',12,NULL,0);

I need to insert in another table values by label when they are not NULL or 0s.
So the final result will be:

label | coord | value |
------+-------+-------+
AAA   |   y   |  7    |
AAA   |   z   |  9    |
BBB   |   y   |  10   |
CCC   |   x   |  12   |

I tried with different OVER/PARTITION but probably I am missing something. Any help?

This is really straight forward:

INSERT INTO new_table
    (label, coord, value)
SELECT label, 'x', x  FROM coords  WHERE x <> 0
UNION ALL
SELECT label, 'y', y  FROM coords  WHERE y <> 0
UNION ALL
SELECT label, 'z', z  FROM coords  WHERE z <> 0 ;

or using LATERAL:

INSERT INTO new_table
    (label, coord, value)
SELECT c.label, v.coord, v.value
FROM coords AS c
    , LATERAL
     ( VALUES ('x', x),
              ('y', y),
              ('z', z)
     ) AS v (coord, value)
WHERE v.value <> 0 ;


Online clustered index rebuild using more log space than expected I have a table TableA

ID PK BIGINT
TrxID BIGINT
CardBalance INT
AccountBalance INT

SP_SPACEUSED 'TableA' output:

name    rows       reserved     data         index_size   unused
TableA  904054184  55547120 KB  34661408 KB  19963544 KB  922168 KB

So the data size is about 33 GB. There is a clustered index on TrxID. There is one nonclustered index, which comes to about 19GB.

My transaction log file size is about 250 GB. DBCC SQLPERF('logspace') shows < 1% usage.

When I run:

ALTER INDEX CIX_TableA_TrxID ON dbo.TableA REBUILD WITH (ONLINE = ON)

The transaction log hits 100% after about 20 minutes and then continues to grow.

Is there a reliable way to calculate how much log space is required for this operation?

Also this seems like a lot of space to use in the log file, does this sound right?

This database is in an Availability Group (so using the full recovery model) with 1 other asynchronous replica.
This is a test database, so there aren't any other transactions running at the same time as the the reindex.

After contacting Paul Randal of SQLSkills he informed me that the reason for the high rates of transaction log usage is that in an ONLINE index re/build each row has it's own log record with all the overhead of a log record. An OFFLINE rebuild logs full 8kb page images, not row by row.

Related link provided by Paweł Tajs:

SQL Q&A: The Lore of Logs (also by Paul Randal)


Table design for child tables with multiple many-to-many relationships We are working on designing tables for a new Networking application, and have been having some problems with finding the best design for the top level tables.

Network is the top level table.
Under the Network there are: Subnet, VirtualLan (VLan), and Virtual Private Lan Service (VPLS).
A Network can optionally have a Subnet, a VLan, and a VPLS. Subnets, VLans, and VPLSs, if they exist, they must belong to a Network.
Many-to-many relationships can exist for Subnets, VLans, and VPLSs.
Subnets, VLans, and VPLSs all have one or more Location(s) (e.g. – Boston, Washington), and if they have any relationships to each other, the Locations amongst the relationships must all agree.

We've taken a couple of runs at this, and keep running into problems when we get down to many-to-many relationships among the Subnets, VLans, and VPLSs and the locations.  we were trying to use an association table that held a foreign key from each of the Subnet, Vlan, VPLS, and Location tables, but we run into problems when we try to create the relationships (like what if there are existing relationships - do we delete those first, and then create new relationships).

Can anyone give me ideas on the best approach at designing these tables?

I'd recommend creating a table which encompasses subnets, VLANs and VPLSs; you can then have a simple self-join table.  If these three concepts each have a significant number of unique fields, you can use vertical partitioning and create a sister table for each of the three.  Using pseudo-code:

* Networks
NetworkID PK

* Locations
LocationID PK

* SubNetworks  -- This concept encompasses SubNets, VLANs, and VPLSs
SubNetworkID PK,
NetworkID  NOT NULL REFERENCES Networks

* SubNetworkLocations
SubNetworkID NOT NULL REFERENCES SubNetworks,
LocationID   NOT NULL REFERENCES Locations,
PRIMARY KEY (SubNetworkID, LocationID)

* SubNets
SubNetworkID PK REFERENCES SubNetworks
-- Additional fields specific for this type of sub-network

* VLANs
SubNetworkID PK REFERENCES SubNetworks
-- Additional fields specific for this type of sub-network

* VPLSs
SubNetworkID PK REFERENCES SubNetworks
-- Additional fields specific for this type of sub-network

* SubNetworkRelationships
SubNetworkA NOT NULL REFERENCES SubNetworks,
SubNetworkB NOT NULL REFERENCES SubNetworks,
PRIMARY KEY (SubNetworkA, SubNetworkB),
UNIQUE (SubNetworkB, SubNetworkA)  -- For efficient querying from the other direction

As with any many-to-many table, be sure your sproc for inserting into SubNetworkRelationships checks for the existence of both { A, B } and { B, A } before inserting a new row.  You can use check constraints to prevent a sub-network from relating to itself.

In this model, there's no RDBMS-level enforcement of the rule "if two sub-networks are related, they must have all of the same locations."  I'm going to go out on a limb and assume that all such tied-together sub-networks must belong to the same Network, too.  If you really want this:

* Networks
* Locations

* SubNetworkGroups
SubNetworkGroupID PK,
NetworkID         NOT NULL REFERENCES Networks

* SubNetworkGroupLocations
SubNetworkGroupID NOT NULL REFERENCES SubNetworkGroups,
LocationID        NOT NULL REFERENCES Locations,
PRIMARY KEY (SubNetworkGroupID, LocationID)

* SubNetworks
SubNetworkID PK

* SubNetworkGroupMemberships
SubNetworkGroupID NOT NULL REFERENCES SubNetworkGroups,
SubNetworkID      NOT NULL REFERENCES SubNetworks,
PRIMARY KEY (SubNetworkGroupID, SubNetworkID)

* SubNets
* VLANs
* VPLSs

A group of SubNetworks belong to one Network but zero or more Locations.  A SubNetwork can belong to multiple groups.  You'd still need to enforce that the various groups a sub-network belongs to all have the same locations, so I'm not sure this buys you much.


SQL Server 2012: NUMA node shown as offline without special affinity settings My main production server has four NUMA nodes with two SQL Server 2012 instances on it.  One of the instances has the CPU affinity set to use only one NUMA node, and sys.dm_os_nodes reflects that correctly. The other instance has no affinity settings, so it should show all 4 NUMA nodes as online, I would think, but it shows Node 3 as offline, which happens to be the node that's online for the other instance. Is there a bug in SQL Server 11.0.3128?

Found the answer:
http://blogs.msdn.com/b/sql_shep/archive/2012/06/21/sql-server-2012-license-core-limitaion.aspx

My consultants installed the wrong version (CAL instead of core), so it's limited to 20 cores.


How to load geometry point tables in sql spatial Need help loading point data in sql spatial.
Similar to this post but I really dont know how to get XY data to be accessible by sql
I want to do what was done in the initial part of this post (Identify points that plot inside the polygon):
https://gis.stackexchange.com/questions/166399/how-do-i-find-points-that-are-not-within-a-list-of-polygons-using-ms-sql
SELECT points.name, polygons.name
    FROM points CROSS JOIN
    polygons
    WHERE  (polygons.shape.STContains(points.shape) = 1)

How do you get the points loaded into your table and sql to recognize the points with out having to define them like the below?
insert into @points values
(geometry::Point(1,1,0)),
(geometry::Point(2,3,0)),
(geometry::Point(5,4,0)),
(geometry::Point(4,2,0))

I am having a hard time defining points without doing it manually like above
Ideally I could build a table like this by selecting the xy data I have in a table already

Its very clunky but you need to use stpointfromtext
geometry::STPointFromText('POINT(xxxxx yyyyyy)', SRID)
where x andf y are the co ordinate values and SRID is the grid identifier for the projection you are using. Wrap it all up with a cursor like the following example
https://support.esri.com/en/technical-article/000012317


Inserting a 30 million row table from a linked server I have a large table that needs to be updated on a nightly basis from a linked server.  Initially I had simply truncated the table and inserted all of the records but this was taking too long.  I'm now using a merge statement to copy over only the records that need updating.  However this appears to still take too long, i.e. 10+ hours  What other methods or techniques can I use to get this table refreshed from the linked server on a daily basis?

BEGIN
SET NOCOUNT ON
MERGE dbo.[ANNOUNCEMENTS] AS Target
USING (SELECT [ANN_ID]
      ,[ANNOUNCEMENT]
      ,[SCH_OR_DIST_NUM]
      ,[START_DATE]
      ,[END_DATE]
      ,[CREATED_BY]
      ,[CREATION_DATE]
      ,[UPDATED_BY]
      ,[LAST_UPDATE_DATE]
    FROM [LINKEDSERVER].[DATABASE].[dbo].[ANNOUNCEMENTS])
            AS source ([ANN_ID]
                        ,[ANNOUNCEMENT]
                        ,[SCH_OR_DIST_NUM]
                        ,[START_DATE]
                        ,[END_DATE]
                        ,[CREATED_BY]
                        ,[CREATION_DATE]
                        ,[UPDATED_BY]
                        ,[LAST_UPDATE_DATE])
    ON (Target.ANN_ID = source.ANN_ID)
    WHEN NOT MATCHED THEN
        INSERT ([ANN_ID]
              ,[ANNOUNCEMENT]
              ,[SCH_OR_DIST_NUM]
              ,[START_DATE]
              ,[END_DATE]
              ,[CREATED_BY]
              ,[CREATION_DATE]
              ,[UPDATED_BY]
              ,[LAST_UPDATE_DATE])
        VALUES (source.[ANN_ID]
              ,source.[ANNOUNCEMENT]
              ,source.[SCH_OR_DIST_NUM]
              ,source.[START_DATE]
              ,source.[END_DATE]
              ,source.[CREATED_BY]
              ,source.[CREATION_DATE]
              ,source.[UPDATED_BY]
              ,source.[LAST_UPDATE_DATE]);
END

MERGE doesn't seem to be what you want here. You're only inserting new rows with a CREATION_DATE from the past 24 hours, right?

SET NOCOUNT ON;

DECLARE @Yesterday DATE = DATEADD(DAY, -1, GETDATE());

INSERT dbo.ANNOUNCEMENTS(column list)
SELECT column list
  FROM [LINKEDSERVER].[DATABASE].[dbo].[ANNOUNCEMENTS] AS r
  WHERE CREATION_DATE >= @Yesterday
  AND CREATION_DATE < DATEADD(DAY, 1, @Yesterday)
  AND NOT EXISTS
  (SELECT 1 FROM dbo.ANNOUNCEMENTS WHERE ANN_ID = r.ANN_ID);

Of course having useful indexes on CREATION_DATE and/or ANN_ID will help.


Cannot generate SSPI Context When I try to connect to a SQL Server from my local machine I get the following error:

  Cannot connect to Computer1\Instance1.
  The target principal name is incorrect.
  Cannot generate SSPI context.
  (Microsoft SQL Server, Error: 0)

When I tried to connect to the same instance through RDP, it throws the following error:

  TITLE: Microsoft SQL Server Management Studio
  Error connecting to 'Computer1\Instance1'.
  Attempted to divide by zero. (mscorlib)

At the same time it is connecting successfully through CMD. It was working fine, until last night. but now I am unable to connect to it.

I have had this happen when it loses a trust relationship with the domain. One of my solutions is to open up a command window and type in ipconfig /registerdns, wait 15 minutes and try again.


How to make SQL Server Agent job history less verbose? So, I've created a SQL Server Agent job, according to the answer to my prev question:

running nightly (at 2:11) according to the schedule:

There are 9 .bak files in 3 subdirectory of d:\backup\ source folder, 3 old bakup files are being deleted and 3 new is being created by another preceding SQL Server Agent job.

The described here job copy-purges files but... what the heck the history of this Copy&Purge BAKs SQL ServerAgent job  shows 96 items:

How to make it less verbose?

Eventvwr.msc doesn't contain any eroors for the corresponding period of time (of job run).

UPDATE:

So, I've made these changes

and it seems it simply truncates a per-job log:

Perhaps you need to uncheck this:

The job step properties screen allows you to prevent the output from the job being recorded in the job history.


With MySQL Replication, what level of resilience is possible? I've found lots of good material on setting up MySQL replication, but not much on what to do in the event of failures. Understanding the degree of resilience I can achieve is fundamental to designing my system, so I'm not here looking for "howto" administrative advice, rather I want to understand the achievable semantics.

So, to simplify my requirements, I have an application that cannot function unless it can read a database. There are two scenarios: first, the common usage, many requests a minute, read the database return an answer.  Second, less freaquently, update the database with new data. It's acceptble for the updates to be a few minutes delayed.

So my first thought:  Master->Slave

Now the reader can use either Master or Slave, if we lose the Master for a while the reader can work against the Slave.

Sounds simple enough. But ... what about more drastic problems, how manual is recovery? How long does it take? What data is lost?

Take this scenario:  Master->Slave. We know that the slave is potantially a little out of synch with the Master. Now suppose we lose the master in a way that means it won't be back any time soon.

Now presumably we need the Slave to become the writable Master, and we'll need a new Slave.

Specific Questions:

How much time and effort to make the Slave the Master - I failed to find docs on what to do. I'm guessing that this is pretty easy. Can we make this take-over seamless to client apps? Adjust DNS routing or some such?
If we can't now get at the old Master's logs, then we have to accept that some updates to the master will never make it to the new Master, we do have data loss?
How much effort to create the new Slave? My guess here is that this is not difficult but potentially takes time. I was trying to imagine reducing this overhead by having two Slaves, and adjusting the replication so that when Slave 1 becomes the new Master, Slave 2 now becomes the slave of that new Master. However given the potential delays in replaction I don't see that ensuring complete consistency is very easy.

A lot of the questions you are asking are predicated by use of master-slave replication. Life's a lot simpler if you use master-master replication; master-slave requires detection of a failure on the master and promotion of the slave. While this can be automated to some extent, you also need to think about how you implement reinstating the master.

Regarding data-loss: get over it. Although there are various approaches implemented in different products, even moving transaction control outside of the database cluster, there is still a possibility of losing data. The only practical solution is to design your system so that its possible to identify failed operations (which is good practice as such losses are more often caused by software bugs than system crashes).

Regarding the details of how you implement the system - a lot depends on the OS you are running on, the impact of downtime, the architecture of the application, the nauture of the network connecting the nodes in the application....lots of information which you've not provided.

There are some useful links here


SQL Server High Disk Queue, Low CPU I'm running a large-ish SQL Server Enterprise 2016 Data Warehouse where we've for some time now had issues where we are seeing the following symptoms:

High Disk Queue length, including 100% activity on those disks. The queues are going over 10,000 at times with very low actual throughput, under 100Mb/s compared to over 1.5Gb/s the drives are capable of.
High Disk Latency, up to 10 seconds per read.
Low CPU usuage, I suspect this is because of the High Disk queue length, and the CPU is simply waiting.

We're running a 24 Core machine, INtel Xeon 6128 (6 cores per socket, 4 CPU's total). We currently got 680 Gb allocated to the VM, with 660 Gb avaiable to SQL.

We're patched to the latest CU, and Windows Patches are more or less up to date.

We're running on a very performant SAN, and haven't seen any IO issues from the SAN point of view that are affecting things.

The only solution we currently have, is to restart the entire machine. Even restarting the service doesn't make a difference. When SQL is stopped disk IO is fine, but once it is restarted (without a server restart), the queue length and latency all go down the toilet.

My current suspicion is around the use CLR integrations and the memory utilisation/garbar colleciton on these, but I haven't been able to find a way to accurately spot this.

I'm at a complete loss of where else to start looking, but currently we're having to restart our server almost weekly due to the issue.

I think best approach is try to find connections, which use most IO.

Look at open connections, see who use most IO.
Look at currently executing commands, what they wait. Would be seen some waits on latches.
After finding who use most IO, it would be easier to make next step in investigation.

As for CLR, there are performance counters which shows quite a lot information about its behavior, so it is possible to look on it.
May be there is usage of swap on the server? Like page/sec significanly above zero? It also may cause such behavior of server.


MySQL backup practices I have a MySQL master-master replication with second master in read-only mode.
My questions reg backup are:

I have 10 databases so should I take daily backup as one dump containing all the databases, if yes when restoring will I be able to restore each database from the full dump which has all the databases? Or taking one db at a time is the good way to go?
Should I take backup from active master or read-only master? If read-only master is the case, then what if before the replication had broken and the backup has been taken from read-only - it will not have the updated data until the replication is active again right? So backing up from active master is fine?
How to go about backing binary logs since im not aware of it much.? What is the process to take binary log backup.?

Thanks!!

A1.

How large are the databases?

If you need logical backup, you can use mysqldump (--single-transaction, --master-data are some options you need to review and use).
For speed, you want to go with mydumper/myloader tool. It will also make sure of taking individual dumps.

Advantage here is you can grab the database you need to restore without having touch other databases of the instance.
If you choose to go with mysqldump, you still can use  mysqldumpsplitter to extract the tables/databases of your choice to export from full dump.

Alternatively, you might want to use the physical backup using Percona's Xtrabackup which provides hotbackup. Here is a post to setup Xtrabackup using Holland framework.

A2.

Backup from Active Master? No!

If we have replication failure on read-only master than priority is to get it fixed and then take the backup. But try to avoid backsups from Active Master.
It's also advised to make sure your backups are stored on a different location other than read-only master!

A3.

Binary log backups provide point-in-time restore. You can store binary logs to a separate partition and have it backedup/snapshot or scripted to copy files to a separate location.

Check out these  two articles: 1 & 2

Hope this is helpful!!


How can I get the MySQL super_read_only setting to work? I can't seem to get the new super_read_only configuration option to work. Logged in as root or a user with SUPER privileges, I can still insert a row. If the user doesn't have super privileges, I get the correct error that the server is running with the -read-only option. Here is an example:

mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.7.10                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| tls_version             | TLSv1,TLSv1.1                |
| version                 | 5.7.10-log                   |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Linux                        |
+-------------------------+------------------------------+
8 rows in set (0.00 sec)

mysql> show variables like '%read_only%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_read_only | OFF   |
| read_only        | ON    |
| super_read_only  | ON    |
| tx_read_only     | OFF   |
+------------------+-------+
4 rows in set (0.01 sec)

mysql> insert into repl1 (value) values ('this should not work');
Query OK, 1 row affected (0.00 sec)

mysql> select * from repl1 where value like '%should not%';
+----+----------------------+
| id | value                |
+----+----------------------+
|  6 | this should not work |
+----+----------------------+
1 row in set (0.00 sec)

This seems to be a consequence of MySQL Bug#79328.  I'm assuming you started MySQL Server with either command-line or configuration file options to enable --super_read_only mode.  Because of the referenced bug, the --super_read_only option is not properly processed at server startup.  Until this is fixed (hopefully soon!), --super_read_only must to be set dynamically.


Use a DOMAIN in multiple schemas The PostgreSQL CREATE DOMAIN command appears to create the domain at the schema level. Is there a best approach I should be aware of that would allow me to  reuse domains across the multiple schemas that comprise my database?

You can store them in the public schema if you're using them in more than one schema. That's what it's there for.

Typically, I don't. If you have a domain in more than one schema that usually indicates to me that either,

the type should be packaged as an extension and in it's own schema.
your two schemas should merge.

But there is nothing wrong with using the public schema.


Fastest way to set up MySQL replication from backup Here's my scenario:

I have a single database on network A that needs to be transferred to network B.
on network B, I need a master-slave setup with 2/3 slaves.
I need to run some significant updates / deletions to the database before it can be put into production again This will probably take a few hours.
Most importantly, I need to minimize downtime.

My current plan is to:

turn off MySQL
backup the MySQL datadir
run my scripts to update the database to the new schema
rsync the entire datadir to all master/slave servers on network B

At this point I have exact copies of the data on all servers. From what I've read, I should then be able to extract the binlog status and start replication.

Does this sound like a reasonable plan, or am I missing something obvious that would a) rule this out or b) make this easier / faster?

For the Sake of this Post

IP of Master is 10.20.30.100
IP of Slave1 is 10.20.30.110
IP of Slave2 is 10.20.30.120
IP of Slave3 is 10.20.30.130

ALL InnoDB
If all your tables are InnoDB, you can do this:
STEP 01 : If you do not have binary logging enabled, you need to enable it. Here is how you can tell:
Login to MySQL and run this
mysql> SHOW MASTER STATUS;

If you get something like this:
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000266 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

then, it is enabled. If you get Empty Set, then it is not enabled.
To enable it, add this to your my.cnf
[mysqld]
log-bin=mysql-bin

and restart mysql
STEP 02 : Create a Replication User on the Master
Run this
GRANT REPLICATION CLIENT,REPLICATION SLAVE ON *.*
TO replicator@'%' IDENTIFIED BY 'replicationpass';

STEP 03 : Test the Replication User
On each of the Slaves, run this in Linux
MASTER_IP=10.20.30.100
mysql -h${MASTER_IP} -ureplicator -preplicationpass -ANe"SHOW VARIABLES LIKE 'hostname'"

Make sure it echos the hostname of the Master from all Slaves.
STEP 04 : Clear all binary logs on the Master
Simply run
mysql> RESET MASTER;

To clear away all binary logs and start with a fresh one
STEP 05 : Dump all your data to a text file
From
MASTER_IP=10.20.30.100
echo "CHANGE MASTER TO master_host='${MASTER_IP}'," > LoadSlaveData.sql
echo "master_post=3306,master_user='replicator'," >> LoadSlaveData.sql
echo "master_password='replicationpass'," >> LoadSlaveData.sql
echo "master_log_file='bogus'," >> LoadSlaveData.sql
echo "master_log_pos=2;" >> LoadSlaveData.sql
DUMP_OPTIONS="-single-transaction --master_data=1 --routines --triggers"
mysqldump --uroot -p ${DUMP_OPTIONS} --all-databases >> LoadSlaveData.sql
echo "START SLAVE;" >> LoadSlaveData.sql

Line 28 will have the binary log and position for replication because of --master_data=1.
STEP 06 : Load the SQL file into each Slave
Login to Each Slave and Load that File
mysql -h10.20.30.110 -uroot -p < LoadSlaveData.sql
mysql -h10.20.30.120 -uroot -p < LoadSlaveData.sql
mysql -h10.20.30.130 -uroot -p < LoadSlaveData.sql

THAT'S IT. NO DOWNTIME
ALL MYISAM or SOME InnoDB / SOME MyISAM
What you proposed in your question is more of a fit for databases with any MyISAM tables
I have recommended the same technique over the years

Nov 09, 2012 : How can I reinitialize some MyISAM tables without restarting MySQL?
Jul 24, 2012 : How to copy a MySQL database which is a mixture of InnoDB and MyISAM to the same server?
Mar 15, 2012 : Why is mysqlhotcopy slower compared to rsync?
Jun 17, 2011 : What is the difference between XtraBackup and rsync?

I even mentioned this in StackOverflow : MySql - create replication with minimal downtime


How can I change the default storage engine in phpmyadmin? I use InnoDB almost exclusively in my applications. However, if I'm not careful when setting up the table, I forget to change it and phpmyadmin sticks me with MyISAM.  Is there a way to change the default storage engine?

You have to add the line default-storage-engine = InnoDB under the [mysqld] section of your mysql config file (my.cnf or my.ini depending on your operation system) and restart the mysqld service.

I don't believe you can change this through PhpMyAdmin.


MYSQL find if a default value has been set for a table (DateTime Specifically) I am trying to internationalize a database, but there are places where the timestamp that gets set is using NOW() instead of UTC_TIMESTAMP(), and it's using Amazon's RDS with multiple MySQL servers.  The problem is that each is on their own timezone and this creates a mess of disentangling the various timezones, translating them to UTC, and then re-translating them back to the users timezone.

This database has some 140+ tables and I'd like something more efficient than searching manually by using SHOW CREATE on each table, plus I'd like to be able to check when I'm done that everything was converted correctly .

Any ideas on a query that would do this?

To be clear, I'm looking for a query to SELECT the rows that I need, I can figure out a INSERT... SELECT statement from that.

Using @a1ex07 answer, I used this query:

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE (table_schema, DATA_TYPE) = ('my_schema', 'timestamp') AND COLUMN_DEFAULT IS NOT NULL;

That showed that many were using CURRENT_TIMESTAMP instead of UTC_TIMESTAMP.


Send Email when sql server agent job fails How can I send an e-mail when the sql server job fails. I added my e-mail as operator and then in the job properties -> notification -> enabled the email. But it is not working. Any ideas?

For 2005 and 2008 you need to configure Database Mail then enable the Agent to use it (SQL Server Agent --> Properties --> Alert System). If problem persists, you can query the system tables related to Database Mail (check BOL for names of tables) for possible more information. If problems still persists you may need to work with your Exchange admin.

For 2000 you need Outlook installed.


What is the fastest way to insert data into MySQL memory table? I need to insert 10M+ records into a MySQL memory table.

A naive approach was to pipe records using shell, e.g.

$ tail -10 ~/Desktop/test.sql
INSERT INTO `test` (`a`, `b`) VALUES (4, 5);
INSERT INTO `test` (`a`, `b`) VALUES (5, 6);
INSERT INTO `test` (`a`, `b`) VALUES (6, 7);
INSERT INTO `test` (`a`, `b`) VALUES (6, 7);
INSERT INTO `test` (`a`, `b`) VALUES (1, 2);
INSERT INTO `test` (`a`, `b`) VALUES (2, 3);
INSERT INTO `test` (`a`, `b`) VALUES (3, 4);
INSERT INTO `test` (`a`, `b`) VALUES (4, 5);
INSERT INTO `test` (`a`, `b`) VALUES (5, 6);
INSERT INTO `test` (`a`, `b`) VALUES (6, 7);
$ mysql -u root -proot test < ~/Desktop/test.sql

The table itself is simple:

CREATE TABLE `test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MEMORY DEFAULT CHARSET=utf8;

In essence, the question is what are some lower level ways of inserting data to MySQL?

You need to batch the inserts. This is done by means of extended inserts.
In your question, you can take the row and make them a single insert as follows:
INSERT INTO `test` (`a`, `b`) VALUES
(4, 5),(5, 6),(6, 7),(6, 7),(1, 2),(2, 3),(3, 4),(4, 5),(5, 6),(6, 7);

Why do it this way ?
When you insert into a MEMORY table, there is still some mild disk I/O to check the table's existence by hitting the table .frm file. I wrote about this 5 years ago (I am using the MEMORY storage engine but MySQL still writes to my disk...Why?)
Your question's example does 10 inserts. That's 10 disk reads to the .frm file and 10 full table locks (the lock granularity for the MEMORY storage engine is a full table lock).
Changing it into a single extended insert, the .frm is read only once and the table locked once.
SUGGESTION
In your case, if test.sql has 10 million rows, there will 10 million tables locks and 10 million access of the .frm file.
You should setup test.sql to batch like 10,000 rows in a single insert. Don't worry, mysqldumps create extended insert like this all the time.
That way, you will have 1000 inserts instead of 10,000,000.
GIVE IT A TRY !!!


Database Space Used Decreased after Index Rebuild (Ola Script) I have rebuilt some indexes which were highly fragmented. After the index rebuild I am seeing my database space used reduced by almost 50% (from 77gb to 33 gb)

Is that normal behavior? I dont have auto shrink turned on - did I lose data?

Note: space used decreased in my database file; the actual physical size of my database file is unchanged.

Command used to calculate the space used:

SELECT sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as Space_Used_MB

Results of select @@version

  Microsoft SQL Server 2014 (SP2-CU11) (KB4077063) - 12.0.5579.0 (X64)

Rebuild of indexes performed using Ola Hallengren's script. Parameters used:

Databases = 'USER_DATABASES',
FragmentationMedium='INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE
',
FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
FragmentationLevel1 = 5, FragmentationLevel2 = 30, FillFactor=80,
UpdateStatistics = 'ALL', OnlyModifiedStatistics = 'Y', LogToTable = 'Y'" -b

This is quite common behavior. You are reclaiming unused space by reordering pages in the leaf level.

Just don't shrink your datafile unless it's critical - you'll probably need that space in the future. You are just wasting time, fragmenting (again) the database and locking tables.


How to determine RAM usage of a query What are the factors that would affect the RAM usage by a query?

How do I assess the rough RAM usage of a query given the complete SQL?

This is regarding the MySQL RDBMS.

MySQL uses RAM in many ways...

For caching data and indexes.  In some sense, it does not matter how much RAM, since it is cache.  This may be the biggest use, but it is a "shared" use.
A complex SELECT may need a temporary table (eg, for GROUP BY and/or ORDER BY and/or subquery).  This may be done in RAM, or it may not even attempt to do it in RAM, but instead do it on disk.  There is one setting that puts a limit on the size of each in-memory temp table.
The parsing of the statement takes a little bit of RAM.
The binlog is buffered through RAM.
"Group commit" is buffered in RAM.
Galera does its "will this commit with a deadlock" logic in RAM.
InnoDB uses bits to keep track of what is locked.

Shall I go on?  Your question is not easy to answer.  Furthermore, I would argue that it is mostly irrelevant.  Would you care to explain why you asked?


How to estimate the I/O operations performed by MySQL queries? On Amazon's RDS, you pay about $0.10 per 1 million I/O requests. I've noticed a very high number (in hundreds of thousands) of I/O requests for a very low traffic database. On seeing this, I did some further research, and found this question which describes that for a 6,000 user website, he was generating 800 million I/O requests a month, which would cost him about $80 / month.

Therefore, I want to know in advance how many I/O operations a MySQL query would generate, and how to optimize / minimize them. Is there any way to estimate how many I/O operations a query would perform, and any general rules I can follow to keep them as low as possible?

Community Wiki answer generated from comments on the question by Raymond Nijland

Use EXPLAIN. This way you can see if an query MAY need disk IO. You need to avoid in the column extra "Using temporary" or ""Using temporary; Using filesort (note the filesort is a misleading name: if the result set fits in memory, quicksort is run in memory)".

This is most likely to be caused by subqueries/unions/order by/group by/... If your result is big and a MyISAM disk-based temporary table is created, and you need to sort the result, you are sorting the result set based on IO reads and IO writes with quicksort algorithm.

In Internal Temporary Table Use in MySQL you can read when MySQL needs to create a disk-based MyISAM table. Maybe you can use avg_row_length * rows (though note that the rows value from explain is not exact with InnoDB engine) to check if the result fits in the heap. See SHOW TABLE STATUS Syntax.

  In general is InnoDB or MyISAM better for avoiding I/O requests?

InnoDB will buffer table data and index data, whereas MyISAM only buffers index keys. An I/O to the table data is needed when explain column extra doesn't say "Using index".

If both are using indexes: With InnoDB, if the buffer is hot it can load data from memory. If indexes need to come from disk there a formula that you can use to calculate needed IO reads for selects, inserts, and updates. From Estimating Query Performance:

  For small tables, you can usually find a row in one disk seek (because the index is probably cached). For bigger tables, you can estimate that, using B-tree indexes, you need this many seeks to find a row:

log(rows) / log(index_block_length / 3 * 2 / (index_length + data_pointer_length)) + 1

InnoDB indexes are larger because it stores data off the PRIMARY / UNIQUE key into an KEY index. This is faster and needs even less IO seeks but you can compress InnoDB data or indexes.


How does SQL Server determine key column order in missing index requests? How does SQL Server determine the order of key columns in its missing index recommendations for a query plan?

When SQL Server creates a missing index recommendation for a particular query plan, it separates possible key columns into 2 groups.  The first set contains all of the recommended columns that are part of an EQUALITY predicate.  The second set contains all of the recommended columns that are part of an INEQUALITY predicate.

Within each set, the columns are ordered by the ordinal position of the columns, based on the table definition.

(Many thanks to Brent Ozar for building a repro script against the Stack Overflow database to prove this!)

1. Create 3 identical tables, but put their columns in different order. (The reason here is to use a variety of column names and datatypes to show that that doesn't impact the column order in the missing index recommendation.)

CREATE TABLE dbo.NumberLetterDate (ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
fINT INT, fNVARCHAR NVARCHAR(40), fDATE DATETIME, AboutMe NVARCHAR(MAX));
GO
CREATE TABLE dbo.LetterDateNumber (ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
fNVARCHAR NVARCHAR(40), fDATE DATETIME, fINT INT, AboutMe NVARCHAR(MAX));
GO
CREATE TABLE dbo.DateNumberLetter (ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
fDATE DATETIME, fINT INT, fNVARCHAR NVARCHAR(40), AboutMe NVARCHAR(MAX));
GO

2. Populate the tables with the same data. Get 100,000 rows from the Users table with real-world data distribution.

INSERT INTO dbo.NumberLetterDate(fINT, fNVARCHAR, fDATE, AboutMe)
SELECT TOP 100000 Age, DisplayName, LastAccessDate, AboutMe
  FROM dbo.Users WITH (NOLOCK)
  ORDER BY Id;
GO
INSERT INTO dbo.LetterDateNumber(fINT, fNVARCHAR, fDATE, AboutMe)
SELECT TOP 100000 Age, DisplayName, LastAccessDate, AboutMe
  FROM dbo.Users WITH (NOLOCK)
  ORDER BY Id;
GO
INSERT INTO dbo.DateNumberLetter(fINT, fNVARCHAR, fDATE, AboutMe)
SELECT TOP 100000 Age, DisplayName, LastAccessDate, AboutMe
  FROM dbo.Users WITH (NOLOCK)
  ORDER BY Id;
GO

3. Write a query that needs an index. Start with 3 equality filters, filtering for an exact value in all 3 fields. Note that all 3 queries have the same fields in the same order:

SELECT ID
  FROM dbo.NumberLetterDate
  WHERE fINT = 100
  AND fNVARCHAR = 'Brent Ozar'
  AND fDATE = '2018/01/01'
  AND 1 = (SELECT 1);

SELECT ID
  FROM dbo.LetterDateNumber
  WHERE fINT = 100
  AND fNVARCHAR = 'Brent Ozar'
  AND fDATE = '2018/01/01'
  AND 1 = (SELECT 1);

SELECT ID
  FROM dbo.DateNumberLetter
  WHERE fINT = 100
  AND fNVARCHAR = 'Brent Ozar'
  AND fDATE = '2018/01/01'
  AND 1 = (SELECT 1);
GO

All three tables have exactly the same data, and the queries are identical. The only difference is the field order - and that's the difference in our missing index requests, too:

In the execution plans, the column order in the missing index request exactly matches the column order in the table. For example, in dbo.NumberLetterDate, the number column is first, so it's first in the missing index request, too:

On dbo.NumberLetterDate, the missing index is on fINT (number), fLetter (nvarchar), fDate, the same order of the fields in the table
On dbo.LetterDateNumber, the index order switches to fNVARCHAR, fDATE, fINT
On dbo.DateNumberLetter, the index order switches to fDATE, fINT, fNVARCHAR

For a single-table operation like this, the index field order doesn't appear to depend on selectivity, datatype, or position in the query. (I leave it to other folks to prove this with more complex queries & joins.)

4. Mix in an inequality filter. On the INT field, for example, put in <> 100 as the filter:

SELECT ID
  FROM dbo.NumberLetterDate
  WHERE fINT <> 100
  AND fNVARCHAR = 'Brent Ozar'
  AND fDATE = '2018/01/01'
  AND 1 = (SELECT 1);

SELECT ID
  FROM dbo.LetterDateNumber
  WHERE fINT <> 100
  AND fNVARCHAR = 'Brent Ozar'
  AND fDATE = '2018/01/01'
  AND 1 = (SELECT 1);

SELECT ID
  FROM dbo.DateNumberLetter
  WHERE fINT <> 100
  AND fNVARCHAR = 'Brent Ozar'
  AND fDATE = '2018/01/01'
  AND 1 = (SELECT 1);
GO

In the execution plans, equality fields go first, then inequality fields - so here, fINT shows up last in all 3 missing index requests because it's an inequality search:

5. Use 3 inequality filters. Use the same search for all fields (<>):

SELECT ID
  FROM dbo.NumberLetterDate
  WHERE fINT <> 100
  AND fNVARCHAR <> 'Brent Ozar'
  AND fDATE <> '2018/01/01'
  AND 1 = (SELECT 1);

SELECT ID
  FROM dbo.LetterDateNumber
  WHERE fINT <> 100
  AND fNVARCHAR <> 'Brent Ozar'
  AND fDATE <> '2018/01/01'
  AND 1 = (SELECT 1);

SELECT ID
  FROM dbo.DateNumberLetter
  WHERE fINT <> 100
  AND fNVARCHAR <> 'Brent Ozar'
  AND fDATE <> '2018/01/01'
  AND 1 = (SELECT 1);
GO

Since there's no equality searches, all 3 fields have the same priority order in the missing index recommendation, and now we're back to sorting purely by field order:


How to shutdown mongodb sharded cluster temporary I have setup mongodb sharded cluster with following topology

3x3 shards (each have 2 replica sets)
1x3 config server replica cluster
2 mongos routers

I want to shutdown whole cluster temporary keeping data integrity intact.
I may use this setup later so I do not want to scrap whole setup.

Any suggestions would be appreciated.

stop balancing - run sh.stopBalancer() from mongos
shutdown mongos services - run db.shutdownServer() from mongos
shutdown config mongod services - run db.shutdownServer() from config server mongod
shutdown shards mongod services - run db.shutdownServer() from replica set mongod


log file very large how to apply changes then truncate sql server 2008 r2 As a developer from time to time I will need to restore a local copy of our live database for testing purposes. I 'm running into disk space issues because the logs are bloody 15gb. I was told the transaction log is basically a log of the operations that were executed on that db and the db never actually updates. It simply reflects whats in the log.

If this is true then how can apply those changes mention in the log then truncate it so it's not 15gb

In order to stop your transaction log growth you need to take regular transaction log backups (along with full database backups).

If it is a test database and you don't need to recover it to a point in time in case of a disaster you can switch the database recovery model to SIMPLE (you probably are using FULL recovery because it is default in SQL Server).

Once you run regular log backups you can use DBCC SHRINKFILE to reduce the file size.

Please read the following question: Why Does the Transaction Log Keep Growing or Run Out of Space? - it provides you with a really good understanding of the problem.


how to solve ORACLE AUDSYS.SYS space error I am having this problem "Warning: odbc_connect(): SQL error: [Oracle][ODBC][Ora]ORA-02002: error while writing to audit trail ORA-55917: Table flush I/O failed for log ID: 1 bucket ID: 0 ORA-01691: unable to extend lob segment AUDSYS.SYS_LOB0000081269C00014$$ by 128 in tablespace SYSAUX ORA-02002: error while writing to audit trail ORA-55917: Table flush I/O failed for log ID: 1 bucket ID: 0 ORA-01691: unable to extend lob segment AUDSYS.SYS_LOB0000081269C00014$$ by 128 in tablespace SYSAUX , SQL state S1000 in SQLConnect in..." is there any known solutions?

ORA-01691: unable to extend lob segment AUDSYS.SYS_LOB0000081269C00014$$ by 128 in tablespace SYSAUX

The SYSAUX tablespace is full.

Because the Audit system can't record what you're up to, it won't let you do anything.

Most immediately, you need [to get your DBA's] to extend the SYSAUX tablespace so that the Audit system has somewhere to write to.

Then [they need to] dig through the Documentation about how to move the Audit Trail data out of the SYSAUX tablespace and into somewhere [slightly] more manageable.

Then [they need to] dig even deeper to figure out how to get rid of the Audit trail data that you no longer want (which is probably the vast majority of it!).


SQL Error [42883] - function does not exist - Cannot call function from trigger I want to insert a new row to Table2 when Table 1 is inserted a row. I have created a function for that. However, when I try to create the trigger, it gives an error although function is created and I can see the function.

SQL Error [42883]: ERROR: function insertintoautoincrementexample() does not exist

Following are the details related to it. I tried to create the trigger with some text parameters but no luck.

//Table 1
CREATE TABLE myOriginalTable(
    companyName VARCHAR(15) PRIMARY KEY NOT NULL,
    location VARCHAR(30)
);

//Table 2
CREATE TABLE autoincrementexample (
    entryid INT4 NOT NULL DEFAULT nextval('autoincrementexample_entryid_seq'::regclass),
    companyname VARCHAR(15) NOT NULL,
    location VARCHAR(30) NOT NULL,
    PRIMARY KEY (companyname)
);

//Function
CREATE OR REPLACE FUNCTION insertIntoAutoIncrementExample(companyname text,location text)

    returns void
    language plpgsql
    as $function$

    declare
    varaudseq bigint;

    begin
        varaudseq :=0;

    begin
        SELECT NEXTVAL(autoincrementexample) INTO varaudseq;
        INSERT INTO autoincrementexample(companyname,location)VALUES(companyName,location);
    COMMIT;
    END;
    END;
    $function$;

//Trigger
CREATE TRIGGER after_insert_original
    AFTER INSERT ON myoriginaltable
    FOR EACH row
    EXECUTE PROCEDURE insertintoautoincrementexample();

Would appreciate any help regarding this.

As the documentation says:

  The trigger function must be defined before the trigger itself can be
  created. The trigger function must be declared as a function taking
  no arguments and returning type trigger. (The trigger function
  receives its input through a specially-passed TriggerData structure,
  not in the form of ordinary function arguments.)

The function you have declared is:

insertIntoAutoIncrementExample(companyname text,location text) ... returns void

so it does not fit both in the return type and in the argument types.
A trigger function does not take arguments but can access the values inserted or changed in the row variables NEW and OLD. They get automatically defined in plpgsql, see Trigger procedures in the plpgsql chapter for  details and examples.

Concerning the error message:

function insertintoautoincrementexample() does not exist

it means: this function name, with an empty list of arguments, does not exist.
The presence of parentheses around nothing is relevant, because in postgresql, functions always go with their argument types:
foobar(int) is not the same function than foobar(), or foobar(int,int) or foobar(text).


Connecting to SQL Server using Oracle SQL Developer I am using oracle sql developer to manage my oracle database. I recently added third party database jdbc drivers to be able to connect to my new MySQL and SQL Server installations. I need help with the right settings for the SQL Server connection i.e UserName, Host Name, Port including formats

I followed these instructions to add the jTDS Java library to Oracle SQL Developer. Basically it's about downloading the jTDS zip, unzipping it in some place you can reach later and then add the jTDS.jar as a JDBC third party driver in SQL Developer ((In the main menu) Tools > Preferences > (On the tree menu in the new window) Database > Third party JDBC drivers > Click on 'Add entry')

I was then able to use my credentials to connect to a SQL Server instance/database using the following connection details. I'm currently using Oracle Developer 4.0.1.14.

The default port for SQL Server instance is 1433, however your SQL Server instance may be running on a non default port. The following site has different methods to determine the port.

http://www.mssqltips.com/sqlservertip/2495/identify-sql-server-tcp-ip-port-being-used/


How to quote a SQL*Plus identifier in a bash script? I get "ORA-00904: "MEMORY_TARGET": invalid identifier" no matter how I try to quote the identifier in the following command:
MEMORY_TARGET=$(su - orat01 -c " echo 'select VALUE from V\$PARAMETER where name = 'memory_target';'| sqlplus / as sysdba ")

Any ideas on how to do this would be greatly appreciated.

[root@o73 ~]# SGA_TARGET=$(su - oracle -c "export ORACLE_SID=RYMIN19; export ORAENV_ASK=NO; . oraenv >/dev/null; echo \"set pages 0
select value from v\\\$parameter where name = 'sga_target';\" | sqlplus -s / as sysdba")
[root@o73 ~]# echo $SGA_TARGET
2147483648
[root@o73 ~]#


How can I take backup MySQL database from one cloud server to another? How can I take backup (by mysqldump) MySQL database from one cloud server to another?

I have a MySQL server that uses innodb and is about 150GB size.

I want to take backup of database to other server, what's the most efficient way to do this?

Clouds can't talk to each other

There is a 3rd machine that can access both of them, correct -- your machine.

your> mysqldump -h cloud1 ... >somefile.sql
your> mysql     -h cloud2 ... <somefile.sql

Or, to be more daring:

your> mysqldump -h cloud1 ...  |
      mysql     -h cloud2 ...

Then you aren't even touching your disk.

Performance will be determined by the bandwidth between you and each cloud.  Adding a gzip in the path won't help because it won't be compressed until it gets to your machine.  Percona's Xtrabackup is a good alternative to mysqldump.  It has compression options and even some parallelism, but, again, you may not be able to take advantage of such.

I avoided the obvious (having one cloud send to the other) because I expect such traffic is forbidden.  Or you can't run commands from a shell script on either cloud.

Connection possible

On either cloud machine, do

mysqldump -h cloud1 ...  |  mysql -h cloud2 ...

If the network is the slow part, then do this on the source machine

mysqldump ... | gzip  |  ssh cloud2 ... "gunzip | mysql"

(I forget the details of how to use ssh to execute the stuff over yonder.)

That could be reversed to run on cloud2:

ssh cloud1 "mysqldump ... | gzip" | gunzip | mysql ...

but it may not matter much.


Audit logins on MySQL database Is there a way to audit logins to MySQL?  I'd like to be able to create a username for each employee and thereby create an audit trail of logins. However, googling has turned up no good results.

The more we can audit, the better.  At the very least, it would be nice to know who logged in when. It would be even better to see who executed what query when. The logs are there mostly to tell clients we have them since there is potentially sensitive information in the database.

Obviously, being able to audit the queries executed by each user (and when) would also give us the ability to better pinpoint who is the cause of an security issue if one should arise.

You would probably want to use the general query log.

  The general query log is a general
  record of what mysqld is doing. The
  server writes information to this log
  when clients connect or disconnect,
  and it logs each SQL statement
  received from clients.

One important thing with logging for security is that an attacker cannot access the log to erase traces of their presence, so consider append-only files.

FWIW in Oracle we can send logs automatically to a remote syslog, but I don't believe MySQL has this feature yet. Perhaps you could fake it with SNMP but I have not tried it.


Query perfomance with multiple JOINs on MyISAM-based DB I'm working with Enron Email Dataset in MySQL database. After I executed data import from the dump file into MyISAM-based DB, I got the set of tables without any connections between them via foreign keys.

I read that MyISAM is more suitable for the case if the tables are more static with lots of select and less update and delete operations. In my case, all queries will be read-only with multiple joins inside of each query. Considering the fact that MyISAM doesn't support foreign keys at all, will this affect the performance of my queries and if yes, how can I optimize them?

Should I convert the DB into InnoDB-format or, it's better to stay with MyISAM-format?

Foreign keys are not required in order to execute a JOIN. Foreign keys are restrictions that are there for security reasons (so that you cannot change the value of a column, or delete/insert a row ending up with an inconsistent state between tables). It is true that in most cases foreign key are used as JOIN matching conditions.

The reasons why InnoDB is recommended is because its support of data integrity thanks to these foreign keys and database transactions. It can also support better concurrency as writes do not need to lock the whole table in order to proceed, only the rows modified. That is why it is recommended for write-loads. If your tables are going to be read-only, transactions, integrity and concurrency are not so important (because you are not going to modify the data), that is why in those cases the simpler MyISAM format can work fine.

InnoDB also has its own buffering management, which can be fine-tuned better than with MyISAM, where its cache works only for indexes, leaving data caching management to the filesystem cache.

There is one case in which foreign key creation can improve your performance: on creation, InnoDB creates a secondary index on the column if it didn't have one beforehand. This functionality is as easy to overcome as creating one on any column that may be referenced on the JOIN.

In summary, InnoDB has many advantages, but in a read-only workload MyISAM can work properly. If you want the data security, however, you will have to implement that on a software layer, easily leading to errors, that is why foreign keys are always recommended.


How to correctly use PFILE keyword in DDS Logical File? (AS400) I'm having trouble wording this question right (also not sure if I'm in the right stack section)

My issue is that I have a logical file ORDFILE located in the ORDRENTLB library, that is suppose have access to the physical file data of CIFFILE located in the CUSTINFOLB library.

When compiling, all I get for the program is the error "ended abnormally".

However, when I remove PFILE(CUSTINFOLB/CIFFILE), and change the type to PF, it compiles perfectly.

The CIFFILE has compiled successfully as its only a PF.

what I have tried:
Moving ORDFILE to the same library as CIFFILE (abnormal ending error)
Tried stating CUSTOMER as a foreign key by placing an F next to CUSTOMER in ORDFILE (got an error)
Tried placing single quotes around 'CUSTINFOLB/CIFFILE', (error)

what am I doing wrong?

thanks for your time.

EDIT

Scope: To create a small distribution system on the iSeries platform.
Create a series of libraries that includes code for the following:

Customer Information File (CIF) Functionality
Order Entry Functionality
Purchasing Functionality
Inventory Functionality

My requirements:

Create 4 DDS Specifications for each of the 4 file groups based on
the information specified.
Create 4 DFU programs for each of the 4 files you set up with DDS
specifications.
Create 4 CL programs to run the DFU programs you have created.

Include some comments as well as a message that will appear on the screen once the menu option has been chosen.  For example, if you chose Option # 1 to Enter CIF data, a message should initially appear such as -
                     'CIF DATA ENTRY PROGRAM IS NOW EXECUTING'

Create 3 Menus for each of the 3 departments(Order Entry/Inventory
/Purchasing) with appropriate titles on each menu.
Create a main menu called MAINMENU to access the 3 department menus.
Create a Query/400 report based on the CIF file and add it to the
main menu as an option.

Ensure that you have followed the 3 requirements as follows:

Create the Query as a report by choosing the  'Print' option.  Format the CIF # to eliminate the ‘,’ from the number on the report. Place a Page heading on the report.

The Main menu should include the following menu options:

Go to the 3 various ‘department’ menus Run the CIF Query program Access the Spool file Sign off

Set up the CLP programs on the menus you have created.

The CIF Master File must only exist in one place, but all departments must be able to access the same file.

  The Database:

  Customer Information File =>

  Customer Number (5 digit numeric field) – KEY

  Company Name

  Contact Name

  City

  Phone number

  Total Customer Order amount ($)

  Order Entry File =>

  Order Entry Number (6 digit numeric field) – KEY

  Customer Number

  Item Number Ordered (10 char.)

  Date Ordered Quantity Ordered

  Ordered By (Person's Name)

  Total amount of Order ($)

  Inventory File =>

  Part Number (6 digit numeric field) – KEY

  Part description

  Quantity on hand

  Unit Cost

  Out of Stock Indicator (1 byte Alpha)

  Purchase File =>

  Purchase Order Number (4 digit numeric field) – KEY

  Part number

  Quantity Purchased

  Quantity Back-ordered

  Purchase Date

  Total Purchase Order Amount ($)

Each of these four things should be given its own PF (aka table).  In the real world, any of them might even be best served by additional supplementary tables, as may be appropriate.  For example, suppose a customer wishes to pace an order for several different items.  This would normally be handled with an order header file, to store data pertaining to the order as a whole, and an order detail file for each item being ordered. But we'll stay within the bounds of your assignment, which has been specifically worded with an expected design.

The tables do not access each others information, per se. But they will contain fields (aka columns) which will refer to key field in other tables.  It is generally a good idea to create indexes on each table, for the various ways we can expect it to be accessed, by creating a simple keyed logical file for each such key sequence.

In this case, after you have defined your ORDFILE, you might define a logical file, called for example ORDFILE1, with CUSTOMER as the [first] key field, perhaps followed by ORDRNUM.  You might have another keyed by ITEMNUM. Whatever key sequences may come in handy can each have it's own LF. Indexes are very low overhead on the iSeries, so having several dozen is not a problem, even though other platforms might have a lower recommend guideline. So in the real world you might want to create a good variety over these files, to help the optimizer with various queries that your users may want.  For an assignment like this, create only those that are relevant to the requirements.

The type of LF that would combine fields from various physicals is called a join (or joined) logical file. Unless your professor has mentioned it, that may be outside the scope of his/her expectations.  A query can read multiple PFs, much as a join logical would, so a join LF may be superfluous in this case.

Disclaimer

Modern best practices on this platform would generally be to use SQL for databases, rather than DDS & Query/400.  And it may be handy to know that QM Query can convert Query/400 queries to SQL.  But most companies still have plenty of DB files defined with DDS. It is important to understand these earlier technologies when you are a professional developer on this platform, since you may have to support them.


Easy way to update Excel data to SQL Server I have a database with some tables "Products, Cores, Clients, ProdCliPrice, etc"

I am developing a software and one of it's functions is to update (or insert) the data in an Excel file to my database.

The Excel file contain a ProductID, a Core Name and a Product Price.

I wrote a code that stores all Core Names and Ids to a array, and then, line by line insert (or update if already exists) the product to the database. Since I have more than 100k rows, it is taking too long.

Could you help me with a better way to insert or update these data?

You coud either use BULK INSERT directly in SQL Server Management Studio like this:

BULK INSERT [DataBase].[dbo].YourTable
FROM 'filepath'
WITH
(
    Firstrow = 1,
    Fieldterminator=';',
    Rowterminator='\n',
    Codepage = 'ACP'
)

For Excel-Files (.xls) you can use this:

SELECT * INTO YourTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=YourFilePath', [ExcelSheet$])

You may have to replace the 8.0 with your current office version (8.0 is for Office 2000, 14.0 is for Office 2010, for example).

There's also SqlBulkCopy in C#.

If you don't want to import all columns, I suggest you write a small program, load the file into a DataTable, drop all the columns you don't need and then use SqlBulkCopy


List of Object properties Is there a function or system view that returns all of the available property names?

I was looking for 'SYSTEMDATAACCESS' and 'USERDATAACCESS' which aren't listed on the MSDN OBJECTPROPERTY page.  I added a comment about them, but am wondering if there are others that I don't know about...given that it's SQL I'd expect them to be available as queryable metadata somewhere, but couldn't find anything.  Are the properties in the meta data somewhere?

EDIT: While it isn't really relevant to my question, here is an example usage of system data access.  select OBJECTPROPERTY(OBJECT_ID('sys.dm_db_stats_properties'), 'SYSTEMDATAACCESS')

Is there a function or system view that returns all of the available
  property names?

No. There isn't even something that returns the names of all of the available built in functions (such as OBJECTPROPERTY itself).

These property values are documented for OBJECTPROPERTYEX even though they do also appear to work for OBJECTPROPERTY and probably should be documented there (the return type is int so there is no reason that they shouldn't be supported there and it is more convenient to work with that function without needing a cast from sql_variant.)

For entirely undocumented property values (such as minlen for INDEXPROPERTY) I'm not aware of any way of determining these.


MySQL, foreign key, can't create table error 150 UPDATE: Sorry, it was because i forgot to add UNSIGNED attribute to the lang_id column.

Original:
I'm trying to create a table with foreign key in MySQL.

I get this error: #1005 - Can't create table 'xy.trans' (errno: 150)

Any ideas whats wrong with the following create statement?

CREATE  TABLE IF NOT EXISTS `lang` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` CHAR(2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE = InnoDB;

CREATE  TABLE IF NOT EXISTS `trans` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `something` TEXT NOT NULL,
  `lang_id` INT NULL,
  PRIMARY KEY (`id`),
  INDEX `index_trans_lang` (`lang_id` ASC),
  CONSTRAINT `fk_trans_lang`
    FOREIGN KEY (`lang_id` )
    REFERENCES `lang` (`id` )
    ON DELETE SET NULL
    ON UPDATE CASCADE
) ENGINE = InnoDB;

I've had a similar problem with this, and I finally resolved it by checking the field types of the two fields that were being referenced - they have to be exactly the same type - all the way down to the "Not NUll" and "Unsigned" settings...

In your original post, the "lang" table has the "id" field defined as "INT UNSIGNED NOT NULL", whereas the "trans" table has the "lang_id" field defined as "INT NULL"...


Can't connect to MySQL NDB manager using ndb_desc I've got a basic MySQL NDB cluster running - it works well.  Manager, data  and SQL nodes are all OK.  1 manager, 2 SQL nodes, 4 data nodes.  2 of the data nodes are from the initial creation, 2 are nodes I'm trying to add.

Environment-wise, this is what I have (applies to all nodes).

OS: CentOS 7
SELinux: disabled
firewalld : not installed
MySQL version: mysql-5.6.28
NDB: ndb-7.4.10

View of the configuration looks like this:

[root@mysql-ndb-manager ~]# ndb_mgm -e "SHOW"
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 4 node(s)
id=2    @10.133.16.108  (mysql-5.6.28 ndb-7.4.10, Nodegroup: 0, *)
id=3    @10.133.16.196  (mysql-5.6.28 ndb-7.4.10, Nodegroup: 0)
id=6    @10.133.16.121  (mysql-5.6.28 ndb-7.4.10, Nodegroup: 1)
id=7    @10.133.16.112  (mysql-5.6.28 ndb-7.4.10, Nodegroup: 1)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @10.133.16.179  (mysql-5.6.28 ndb-7.4.10)

[mysqld(API)]   3 node(s)
id=4    @10.133.16.117  (mysql-5.6.28 ndb-7.4.10)
id=5    @10.133.16.180  (mysql-5.6.28 ndb-7.4.10)
id=8 (not connected, accepting connect from any host)

NoOfReplicas is set to 2, so this node count should be OK.  I realised pretty quickly that NoOfReplicas set to 2 means adding a single additional data node doesn't work.

As you can see, I have an available node ID (8) that can be allocated for things like ndb_desc, etc.

New nodes I'm trying to add:

IDs: 6 & 7

They are configured properly, as far as I can tell, but have no data on them.  They've started properly and are listed as part of the cluster.  It's the redistribution process that isn't working for me.

The docs are pretty easy to understand, i.e. they require the use of ndb_desc to connect to the manager and alter the table structure so that the data is distributed to the new nodes.

When I run the required commands, I get this:

[root@mysql-ndb-manager ~]# ndb_desc -c 10.133.16.179:1186 my_table -d
appdb -p --ndb-nodeid=8
Unable to connect to management server.

NDBT_ProgramExit: 1 - Failed```

I've seen others with similar issues, and it nearly always seems to be the lack of a node ID that can be allocated.  This isn't the case for me, as far as I can tell.

I've tried connecting with the manager's IP address, localhost, 127.0.0.1.
 I've tried locally and remotely.  I've tried using the exact command from the docs, i.e. their database and table names but that doesn't work (as expected, I guess).

The only data I have right now is as follows:

Database: `appdb`
Table: `my_table` (ID auto_increment and `name` char(25))
Data: 14 rows, random names

Data is replicated between the old/working nodes without issue, i.e. I can create a database or run any SQL command on one of them and the change is immediately reflected on the other node.  Offline nodes show up as offline as expected and show up as online when I bring them back up.

I've also got a rolling restart script that works without any issues so it looks like the clustering side of things is OK.

Lastly, here's my manager's configuration file:

[ndb_mgmd default]
DataDir=/var/lib/mysql-cluster

[mgm]
HostName=10.133.16.179

[ndbd default]
NoOfReplicas=2
DataMemory=256M
IndexMemory=128M
DataDir=/var/lib/mysql-cluster

[ndbd]
HostName=10.133.16.108

[ndbd]
HostName=10.133.16.196

[mysqld]
HostName=10.133.16.117

[mysqld]
HostName=10.133.16.180

[ndbd]
HostName=10.133.16.121

[ndbd]
HostName=10.133.16.112

[mysqld]

What else could be causing this?

Thanks!

There is nothing in the description that points to the error.
Everything looks fine. So would be good if you could post
the error messages written in the cluster log when you
start ndb_desc.


How to delete records with FK constraint using Delete Trigger I'm learning MS SQL Server and trying to implement a trigger to delete records in the child table  if the record in the parent table is deleted. I'm using AdventureWorksDW database provided my Microsoft.
I have two tables DimProductSubcategory and DimProduct. So if I delete a product category in DimProductSubcategory, all related records in DimProduct should also be deleted.
So I have created a trigger:
CREATE trigger on_delete_trigger
    on DimProductSubcategory
    after delete
    as
    begin
        SET NOCOUNT ON;
        DELETE FROM DimProduct WHERE (DimProduct.ProductSubcategoryKey IN (SELECT DimProduct.ProductSubcategoryKey FROM DimProductSubcategory))
    end

But when I try to delete a record in DimProductSubcategory I get:
The DELETE statement conflicted with the REFERENCE constraint "FK_DimProduct_DimProductSubcategory".
The conflict occurred in database "AdventureWorksDW2019", table "dbo.DimProduct", column 'ProductSubcategoryKey'.

I understand the meaning of the error message, but I do not understand the reason for it. I thought the trigger was supposed to delete all child records so that I can delete the parent record without violating data integrity.
Although I'm not 100% sure I got my DELETE statement right.
So how can I implement a trigger to delete child records when a parent record is deleted?

The problem you originally had was that you were not referencing the deleted pseudo-table. You were essentially saying "Oh, delete subcategory 5? Ok, delete all products that have a subcategory!"
I think you want this in your trigger:
DELETE p
  FROM dbo.DimProduct AS p
  INNER JOIN deleted AS d
  ON p.ProductSubcategoryKey = d.ProductSubcategoryKey;

I still don't believe you want to delete every product that belongs to a subcategory you are removing, but hopefully this is just an exercise and not real business logic you want to implement.


Listing active PostgreSQL foreign data wrappers I have a few foreign data wrappers set up from my main PostgreSQL database to other databases.  Is there a table I can query to list all of the FDWs? Unfortunately select * from information_schema.schemata doesn't give any hints about which schemas are foreign and which are local.

The information you're looking for is in various pg_catalog tables. pg_class is the main table that represents all relations, normal or foreign; you can limit it to foreign tables by including WHERE relkind='f'. Information on the foreign data wrappers themselves is in pg_foreign_data_wrappers, on servers in pg_foreign_servers, and on foreign tables in pg_foreign_tables.
Start with select relname from pg_class where relkind='f' to just get a list of table names, and from there you can use the pg_catalog docs to find whatever other pieces of information you need for what you're trying to do.


How to query all entities associated with all children (recursive) of a node in SQL? This question is about SQL in general. Answering specifically for MySQL would be helpful but not necessary.

Ok, I’m having trouble putting this into words… so bear with me.

Say I have a tree of things (I’ll call them nodes), with a table that looks something like this (the structure can be changed; this is just a simple version):

+---------+-----------+
| node_id | parent_id |
+---------+-----------+
| 1       | NULL      |
| 2       | NULL      |
| 3       | 1         |
| 4       | 1         |
| 5       | 5         |
| 7       | 2         |
| 8       | 5         |
+---------+-----------+

Then I have a table of entities. Each entity is associated with a certain node. For example (again, the structure can be changed):

+-----------+---------+
| entity_id | node_id |
+-----------+---------+
| 1         | 1       |
| 2         | 1       |
| 3         | 4       |
| 4         | 7       |
| ...many more rows   |
+-----------+---------+

This structure could represent a lot of things, e.g. each entity is a movie, while each node is a genre (but there can be unlimited levels of sub-genres).

So retrieving all the entities for a given node is simple; just running a query on the entities table for a specified node_id.

Here’s my question: how would I query the entities table for all entities associated with a given node, and all it’s children nodes (every level, recursively). In the movies example, I want to find all movies for a particular genre and all its sub-genres, and its sub-genres, etc.

I mentioned the word recursive, but that doesn’t mean that the query should be recursive, but conceptually it is. The query should be as fast as possible. The structure of the tables may need to be changed as well.

Thanks for your help!

I assume the row (5,5) is a typo, so I used:

create table tree (node_id int not null primary key, parent_id int);
insert into tree (node_id, parent_id)
values (1,null),(2,(null),(3,1),(4,1),(5,4),(7,2),(8,5);

as a side note, which representation would you have preferred if you were about to start answering a question, the ascii art in your question, or create and insert statements like above?

To recursively traverse the tree you can use a recursive common table expression (CTE):

with rec (node_id, ancestor_id) as (
    select t.node_id, t.parent_id
    from tree t
    union all
    select rec.node_id, t.parent_id
    from rec, tree t
    where rec.ancestor_id = t.node_id
) select * from rec

I much prefer explicit joins, but the DBMS I'm using does not support them in CTE:

with rec (node_id, ancestor_id) as (
    select t.node_id, t.parent_id
    from tree t
    union all
    select rec.node_id, t.parent_id
    from rec
    join tree t
        on rec.ancestor_id = t.node_id
) select * from rec

Now it's just a matter of joining this result with your entities table:

with rec (node_id, ancestor_id) as (
    select t.node_id, t.parent_id
    from tree t
    union all
    select rec.node_id, t.parent_id
    from rec
    join tree t
        on rec.ancestor_id = t.node_id
) select rec.ancestor_id, e,entity_id
  from rec
  join entities e
      on e.node_id = rec.node_id

This assumes that your DBMS version supports recursive CTE:s. The latest versions of MySQL / MariaDB does.

Another side note, you may want to normalize the tree relation, and keep the structure in a separate relation:

create table treenode (node_id int not null primary key, ...)
create table tree (node_id int not null primary key
                  ,parent_id int not null);

root node(s) are the nodes that are absent in tree

If you are on an older version, or have huge amounts of data and your performance needs to be improved you can add additional information to your model. The most common ones are:

Nested Sets, all nodes have an interval of what nodes they sub-sume
Materialized Path, all nodes have an aggregated string which represents ancestor nodes
A transitive closure table, I have some notes and a few examples at tree:


Delete orphaned SQL Users I have a Microsoft SQL Server that had a database called SQLDATABASE. It also had a user called SQLUSER. The SQLDATABASE was deleted but not SQLUSER. SQLUSER only belonged to SQLDATABASE. I want to know all of the "Broken" users (e.g. SQLUSER) that is not part of a SQL database so that I can "clean house". I can go into every sql user and delete them by looking to see if they are attached but that seems very inefficient and something a script can help with. This is what I am asking.

The server has 200 SQL Databases but 250 SQL Users. I want to remove the extra 50 SQL Users as they aren't being used and there is no reason to have them.

I am trying to find a script to either let me find (and delete) these SQL Users or at least identify so I don't have to go into each of the SQL Users individually.

There seems to be some confusion over terminology. A user exists at the database level and is removed when you drop a database. A login exists at the server level and is mapped to a database user to get access to the database.

I'm assuming you want to find all logins that are not mapped to a database user and are therefore potentially no longer in use. The below query will achieve that:

CREATE TABLE ##UnusedLogins (
    [Login] NVARCHAR(255),
    [InUse] BIT
)

INSERT INTO ##UnusedLogins ([Login], [InUse])
SELECT [login].[name] AS [Login],
    0 AS [InUse]
FROM sys.server_principals [login]
WHERE [login].[name] NOT LIKE 'NT SERVICE\%'
    AND [login].[name] NOT LIKE 'NT AUTHORITY\%'
    AND [login].[name] NOT LIKE '##%'
    AND [login].[type_desc] <> 'SERVER_ROLE'
    AND [login].[name] <> 'sa'

EXEC sp_msforeachdb 'UPDATE ul
SET [InUse] = 1
FROM ##UnusedLogins ul
INNER JOIN sys.server_principals [login] ON [login].[name] = ul.[Login]
INNER JOIN [?].sys.database_principals [user] ON [user].[sid] = [login].[sid]'

SELECT * FROM ##UnusedLogins

DROP TABLE ##UnusedLogins

Any login in the output that has a 1 in the InUse column is mapped to a database user. Any login with a 0 is not mapped. This does not mean the login is not in use, simply that it is not mapped to any database user.


Primary key should be chosen so as attributes never change? I was reading about keys in this DatabaseSystems book -

The primary key should be chosen such that its attributes are never, or very rarely, changed. For instance, the address field of a person should not be part of the primary key, since it is likely to change.

Can someone provide an explanation to this ?

Is it because a change might be a danger to its inherent requirement of being unique ?

I cant really make out the proper logic for this.

Keys are identifiers. They permit database users to identify the specific things about which information is being recorded in a database. If the values of key attributes are subject to change then there may be a risk of misidentification, particularly if the identifying information is also recorded elsewhere outside the database (e.g. in other databases, documents or in the mind of the users).

Sensible criteria to consider when choosing what keys to use are: Stability, Simplicity and Familiarity. These are good criteria for choosing any key, not just "primary" keys (the designation of a "primary" key is essentially arbitrary and a matter of convention or convenience - it doesn't mean those keys are fundamentally different to any other).

Stability, simplicity and familiarity are of course all subjective and relative criteria. Any attribute, whether key or not, might need to change and sometimes the ability to change a key attribute is a necessary part of maintaining accurate data and the correct operation of the systems that depend on the data. Very often there is no way to foresee a future requirement that may require such a change. It isn't necessary or even achievable to expect all keys never to change.

Uniqueness isn't really the issue because uniqueness needs to be enforced whether key values change or not. The uniqueness is implicit in the fact that it is a key and is maintained as such by the DBMS.


Is it better to store images in a BLOB or just the URL? Possible Duplicate:
  Files - in the database or not?

I was wondering if there's any good reason to still use blob fields in a database. A couple of years ago I worked with a DB with a bunch of images in it, the DB was very slow and I couldn't see any good reason to keep the images inside the DB, so I got the images out and stored the filenames instead.

Was this a smart move? What would you do in my place?

As you can tell from the other answers, this is a big "It Depends." Some other factors might be if you are paying for hosting, do they charge more for file storage or database storage. File storage is typically cheaper, especially for cloud services.

If you are self hosted and using SQL Server, the upcoming version, codename Denali, will extend FILESTREAM to allow access via both TSQL and file system. It also will make sure these stay in sync. You can access, update, delete from either side and it will keep everything organized.

Do your research and find out what's important to you and pick the direction based on that choice.


MySQL configuration with 256GB I need suggestions for my.cnf optimization. Currently I have a server with 256GB Ram and 40(CPUs) along with website hosted on this server.

My Wordpress database size is around 1GB and using myISAM tables.

[mysqld]
bind-address    = 127.0.0.1
performance-schema=1
# GENERAL #
user                           = mysql
default-storage-engine         = InnoDB
socket                         = /var/lib/mysql/mysql.sock
pid-file                       = /var/lib/mysql/mysql.pid
#tmpdir                = /tmp
tmpdir                 = /dev/shm

# MyISAM #
key-buffer-size                = 1024M
myisam-sort-buffer-size        = 512M
join_buffer_size = 4M
read_buffer_size = 4M
sort_buffer_size = 4M
read_rnd_buffer_size = 4M
myisam-recover                 = FORCE,BACKUP

# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000
skip-external-locking
skip-name-resolve
# skip-networking

# CACHES AND LIMITS #

tmp_table_size=512M
max_heap_table_size=512M
query_cache_size=128M
query_cache_type = 1
query_cache_limit=3M
wait_timeout=60
interactive_timeout=60
thread_cache_size=128

#tmp-table-size                 = 32M
#max-heap-table-size            = 32M
#query-cache-type               = 1
#query-cache-size               = 0
max-connections                 = 20000

#thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 4096
table-open-cache               = 16384

# INNODB #
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 2048M
innodb_buffer_pool_instances = 2
innodb_log_file_size = 1G
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_thread_concurrency = 0
innodb_read_io_threads=16
innodb_write_io_threads=16

# SLOW QUERY
slow_query_log=1
slow_query_log_file="/var/log/mysql-slow.log"
long_query_time =10

#max_allowed_packet=268435456
max_allowed_packet=524288000
open_files_limit=30000
innodb_file_per_table=1

Suggestions for your my.cnf-ini [mysqld] section from the limited available information provided,

max_connect_errors=10  # from 1000000 - allow hackers/crackers nn pw guesses
sort_buffer_size=4M  # REMOVE or lead with # for default
read_buffer_size=4M  # REMOVE or lead with # for default
read_rnd_buffer_size=4M  # REMOVE or lead with # for default
thread_cache_size=100  # from 128 see V8 REFMAN for CAP to prevent OOM

Take 2 minutes with MySQLCalculator.com to see what your values have done to the RAM footprint requirements that are extreme and will negatively impact performance.

Eliminate the duplicate variables in your my.cnf-ini and be consistent with underscores rather than mixing in hyphens.

Provide the additional information requested a few hours ago for additional helpful suggestions to improve performance.

Good luck with this new venture on this nice equipment configuration.  Any SSD's?


Get data from database and put it back to a new datasheet I want to get the data from the database and put it back with the new format

I have a lot of datasheet like follow

ID a b c  d
1  1 5 9  13
2  2 6 10 14
3  3 7 11 15
4  4 8 12 16

and I want to put it in a new datasheet like follow

ID a b c
1  1 5 9
2  1 5 9
3  1 5 9

and the second sheet's data's column C can be the column D like

ID a b c d
1 1 5 9 10
2 1 5 9 10
3 1 5 9 10

how to make the SQL?

This returns the results you you specified, but I'm not sure it returns the results you want.  It was built on Oracle, so you may need to modify the syntax slightly and of course the table names.

CREATE TABLE T1 (ID Number(3), A Number(3), B Number(3), C Number(3), D Number(3));
CREATE TABLE T2 (ID Number(3), A Number(3), B Number(3), C Number(3));
CREATE TABLE T3 (ID Number(3), A Number(3), B Number(3), C Number(3), D Number(3));

INSERT INTO T1 values (1, 1, 5,  9, 13);
INSERT INTO T1 values (2, 2, 6, 10, 14);
INSERT INTO T1 values (3, 3, 7, 11, 15);
INSERT INTO T1 values (4, 4, 8, 12, 16);

INSERT INTO T2 (
   SELECT T1B.ID, T1A.A, T1A.B, T1A.C FROM T1 T1A
   CROSS JOIN T1 T1B
   WHERE T1A.ID=1
);

INSERT INTO T3 (
   SELECT T1B.ID, T1A.A, T1A.B, T1A.C, (SELECT T1C.C FROM T1 T1C WHERE T1C.ID=2)
   FROM T1 T1A
   CROSS JOIN T1 T1B
   WHERE T1A.ID=1
);

SELECT * FROM T2;
SELECT * FROM T3;