Does anyone use modules in DB2 LUW? Our environment is currently DB2 9.7 Fix Pack 4 on AIX.

I am in the processing of designing and coding some SQL PL stored procedures responsible for archiving data into an archive schema and then purging out old/un-needed data from the main schema.

I created some user defined types (UDTs), specifically row types and cursor types. I am looking at dividing my logic into three stored procedures. Proc A would be used to purge a particular set of data related to my main EVENT table (ie, the EVENT and everything underneath it). I don't really want others to be able to call this. I just want to modularize the logic. Proc B would archive data from EVENT (and other tables) that the end user wants and then call into Proc A to clean up the original data. Proc C would be used to look for EVENTs that could be discarded and then call into Proc A for actual work.

Now I could create all of these independent of each other, and then deploy them that way. Or....I could use a module. From what I understand a module allows me to associate related functionality (types, stored procs, functions, etc.) together. It also allows me to decide which objects I wish to be accessible external to the module, ie, can be called/used directly, versus which ones can only be called internal to the module (such as the case above, where I could hide stored Proc A, but allow B and C to call it).

This sounds incredibly intriguing. But it has its downsides too. I'm guessing it isn't part of any SQL standard, but something specific to IBM DB2. Even as such, you cannot directly interact with modules via Data Studio or Control Center (other than CALLing the exteral facing procedures/functions). And you cannot create them directly in IBM's InfoSphere Data Architect either. So I would have to maintain the DDL's independently.

So the module concept sounds handy. I can hide some things and choose what can be called to make sure someone doesn't misuse anything.

But, given it doesn't seem to be supported by IBM tooling outside of command line....I was wondering....who all uses modules (specifically in DB2 LUW)? Does anyone? Does it makes sense? If not, is there another way I could accomplish my thought of modularizing code while making the "modules" inaccessible for direct calling?

I won't be able to answer all your questions, but for what it's worth:

SQL modules seem to be a part of the SQL/PSM 2008 standard. At least they appear in the draft version, that can be found on the interwebs.
Tools will eventually catch up with the server features. Meanwhile, you can use the Oracle-compatible PL/SQL dialect, currently supported by tools, to create PL/SQL packages, which are essentially the same thing.
Yes, people do use module. Many of the DB2 system-provided routines are implemented in modules.
While you can restrict direct invocation of routines outside modules by granting appropriate privileges, you cannot "modularize" anything without "modules".


Mysql federated engine table creation error I am trying to create a federated table on my local machine which suppose to connect to my another machine which also in my local network. I have enabled federated engine on both machines and is shown when I run this command show engines.

Below is my syntax.

CREATE TABLE fedTransfer( `fedID` INT NOT NULL AUTO_INCREMENT, `transferID` INT, `outletFromID` INT, `employeeToID` INT, `transferStatus` ENUM('y'), `transferToDate` DATE, `transferToTime` TIME, PRIMARY KEY (`fedID`) )
ENGINE=FEDERATED;
COMMENT='mysql://root:passw1@192.168.2.103/mptest/fedTransfer';

Below is the error I get when I run this on sqlyog.

Error Code: 1
Can't create/write to file 'server name: '' doesn't exist!' (Errcode: 1408685900)

Execution Time : 0 sec
Transfer Time  : 0 sec
Total Time     : 0.047 sec
---------------------------------------------------

Query: COMMENT='mysql://root:passw1@192.168.2.103/mptest/fedTransfer'

Error Code: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'COMMENT='mysql://root:passw1@192.168.2.103/mptest/fedTransfer'' at line 1

Execution Time : 0 sec
Transfer Time  : 0 sec
Total Time     : 0 sec
---------------------------------------------------

OBSERVATION #1
According to the Book

Page 434 shows an example using COMMENT to establish a federated connection. Unfortunately, the book was written when MySQL 5.0.13 was released. Now, you use CONNECTION instead of COMMENT,
Here are my past answers (over a year ago) where I use CONNECTION instead of COMMENT:

Jun 28, 2012 : Connecting remote MySQL database to local MySQL database?
Jan 04, 2012 : fetching externally hosted db's table from within the local mysql server

OBSERVATION #2
You have this:
CREATE TABLE fedTransfer( `fedID` INT NOT NULL AUTO_INCREMENT, `transferID` INT, `outletFromID` INT, `employeeToID` INT, `transferStatus` ENUM('y'), `transferToDate` DATE, `transferToTime` TIME, PRIMARY KEY (`fedID`) )
ENGINE=FEDERATED;
COMMENT='mysql://root:passw1@192.168.2.103/mptest/fedTransfer';

Remove the semicolon from the back of the word FEDERATED
EPILOGUE
Your code should look like this:
CREATE TABLE fedTransfer( `fedID` INT NOT NULL AUTO_INCREMENT, `transferID` INT, `outletFromID` INT, `employeeToID` INT, `transferStatus` ENUM('y'), `transferToDate` DATE, `transferToTime` TIME, PRIMARY KEY (`fedID`) )
ENGINE=FEDERATED
CONNECTION='mysql://root:passw1@192.168.2.103/mptest/fedTransfer';

Give it a Try !!!


Should indexes automatically be placed into their own bufferpool? We work with DB2 LUW (specifically we are currently on 9.7 FP4) running on AIX.

Per best practices from IBM, they recommend that when you build a table, you place the data, the indexes, and LOBs/LONG data each into their own tablespace. (Reason is better control over disk, maintenance, backups, etc.)

Tablespaces have to be associated to bufferpools. Right now most of our tables and indexes fit into 4K bufferpools and tablespaces.

Normally with our scripts we set up a 4K bufferpool BP4K. We then set up at least two 4K tablespaces (as we dont' have a lot of LOBs): TS_DAT_4K for data and TS_IND_4K for indexes.
By default we have just been assigning both of these tablespaces to bufferpool BP4K.

What I have wondered: Since indexes are in their own tablespace, should they also be given their own bufferpool?

My reason for wondering this is based on optimization. If indexes have their own bufferpool, then they are more likely to remain in memory (rather than being pushed out of memory due to table records being read in). This would allow for faster scanning of indexes to find the records in the tables. And since indexes would no longer share the same bufferpool with the tables, more of the tables can now remain in memory for logical retrieval versus a physical read. So my thought is that this would reduce physical I/O and thus help with the performance/maintenance of the database.

I also can't help but wondering if this is just pre-optimization, which is 99% of the time a bad thing to do, introducing extra overhead, etc. (especially since we haven't identified that we need separate bufferpools yet based on current performance. Of course the application under development isn't in production yet either and does still need database tuning....)

Thoughts on this? Is this a best-practice? or just pre-optimization and over-thinking?

Lets run two hypothetical databases (H1 and H2) in parallel, with the same total amount of RAM for bufferpools (R).

Let H1 have a single bufferpool of size R.
Let H2 have two bufferpools: one of size I for index pages, the other of size D for data pages. (And D+I==R of course.)

The question is:

How do you determine the right values for I and D to make H2 perform better than H1?

My answer is that you can't in general. The database engine for H1 has more room for optimizing its cache than H2. If there are times of day where more index pages would lead to better performance, it can discard unused data pages and have a "mostly index pages" cache. If at a later time, data pages become hotter, it can evict more index pages and have a "mostly data pages" cache.

H2 can't do that. Once it has I pages-worth of index pages cached, it can't cache more even if that's what would have been best right now. It's stuck there with a sub-optimal use of RAM.

The only way for H2 to perform just as well as H1 is if the D/I split initially chosen is ideal, and the workload is very stable. That sure can happen, but I'd wager that it is not a very usual database scenario. If that's not your case, think of H1 as being the same as H2 but with dynamic partitioning of the cache between data and index pages managed directly by the thing that knows the most about how it needs to optimizing I/O (i.e. the database engine).

This is not to say that maintaining different bufferpools is never a good idea.
One scenario for isolating specific pages in a specific cache I've encountered is having a critical "report" that needed to run fast (obviously) at all times, and happened to use a few tables that were pretty much never used elsewhere. So those pages kept getting evicted, and that "report" had runtimes that varied a lot from run to run. Moving a specific set of tables (and their indexes) to a specific pool removed much of the "non-deterministic" performance part.
But that's sub-optimal for the database as a whole, more of a kludge and nearer Voodoo optimization IMO. (And that wasn't on DB2 but I believe that's irrelevant here.)

So my advice is: if you have X Gb of RAM available for cache, use a single buffer, put everything in it, and let the database engine do its magic in there.
If you run into a corner case that would appear to benefit from cache segregation, try it out, benchmark it, think about the overhead of having to maintain the magic numbers for each cache size, and go tune the queries, schema or disk layout instead :)


Why is cross db ownership chaining on by default for system DB I am reading about cross db ownership chaining Server Configuration Option

It talks about how and why to use (or not) this option and says

  Setting cross db ownership chaining to 1 is not recommended unless all of the databases hosted by the instance of SQL Server must participate in cross-database ownership chaining and you are aware of the security implications of this setting.

It also offers this code to check.

SELECT is_db_chaining_on, name FROM sys.databases;

According to Server Configuration Options (SQL Server) the defualt value for 'cross db ownership chaining' is 0 (zero)

When I run it on my servers, old, new or whatever they all seem have it enabled on master, tempdb, and msdb.  I checked a few from SQL 2012 to SQL 2017, Enterprise and Standard editions.

I did find that it where it is set to on by default but not why.

tempdb settings Can not be altered
master settings Can not be altered
msdb settings Can be altered

If it is bad, why is it on by default on system databases?

Why am I not finding anything talking about why it is on by default on system databases?

I have continued to search for reasoning behind this.  Everything I find is some version of

  These system databases must have cross-database ownership chaining turned on in order to function properly source

Clearly the system databases; master, tempdb, and msdb are interdependent. Presumably early in SQL development, some microsoft developer made the choice to use cross-database chaining to solve access issues, and it has never been updated.

Another of of those things that is Microsoft recommends not be used, but they are dependent on it.


OS error 13 when running mariabackup as non-mysql user All:

I'm trying to set up regularly-scheduled backups of our MariaDB cluster with mariabackup, and have set up an account for this purpose.  The account is a member of group mysql. But when I run the backup as that user:

$ mariabackup --backup -u [myid] -p [password]
mariabackup: Generating a list of tablespaces
2017-12-08 17:49:07 140352620800128 [ERROR] InnoDB: Operating system error number 13 in a file operation.
2017-12-08 17:49:07 140352620800128 [ERROR] InnoDB: The error means mysqld does not have the access rights to the directory.
mariabackup: error: xb_load_tablespaces() failed witherror code 11

If I run with sudo -u mysql, the exact same command works fine.

Is there a way to turn on extra debugging or verbosity so I can see WHICH file or directory it's having trouble accessing?

MariaDB v10.2.11, CentOS 7 (x86_64)

I was able to use the strace command to figure out what was going on. There were two problems. The first is that there was no group write permission on the output directory, so it failed to create the xtrabackup_backupfiles directory. The second is that there were two database directories in /var/lib/mysql that lacked group r-x permission. By fixing these two issues, I was able to get it to work.

Still, it would be nice if the error message actually indicated what specifically it didn't have permission on, rather than simply reporting that it lacked permission for something.


How do I detach all other users from a postgres database? I need exclusive access to a database. Is it possible using an SQL command to "detach" all other users from a postgres database. Or maybe closing all other connections and then gaining exclusive access.

This is for unit testing, and tests are only run manually, so there is no danger involved. Only old dead connections will be affected.

There are no other users connecting to these unittest databases.

The old dead connections come from developing. This happens all the time when a test that is being written or fails does not exit clean.

If someone also needs to keep locked out other users for a while after disconnecting them in a production scenario, see Scott Marlowe's answer below: https://dba.stackexchange.com/a/6184/2024

See also this similar question on dba: How to drop all connections to a specific database without stopping the server?

You could try connecting to the database as the postgres user and running:

SELECT pg_terminate_backend( procpid )
FROM pg_stat_activity
WHERE procpid <> pg_backend_pid( )    -- 1. don't terminate your own session
    AND datname =                     -- 2. don't terminate connections to
    (SELECT datname                   --    other databases in the cluster
       FROM pg_stat_activity
      WHERE procpid = pg_backend_pid( )
    );

update
An even better query gets rid of the subselect:

SELECT pg_terminate_backend( procpid )
FROM pg_stat_activity
WHERE procpid <> pg_backend_pid( )
    AND datname = current_database( );


When are we supposed to set default-character-set for the client? I'm trying to understand when I'm supposed to set/force default-character-set for the client.

The documentation states:

  You can force client programs to use specific character set as
  follows:

  [client] default-character-set=charset_name

  This is normally unnecessary. However, when character_set_system
  differs from character_set_server or character_set_client, and you
  input characters manually (as database object identifiers, column
  values, or both), these may be displayed incorrectly in output from
  the client or the output itself may be formatted incorrectly. In such
  cases, starting the mysql client with
  --default-character-set=system_character_set—that is, setting the client character set to match the system character set—should fix the
  problem.

Let's assume charater_set_system and character_set_server are different.

My first question:

I suppose that when I'm importing an export files generated by mysqldump the variable default-character-set has no effect as set names is always issued at the very beginning of the export file.
Am I right or there could be corner cases?

The second question:

What about manually running sql scripts? The documentations states
that some characters may be displayed incorrectly. How can I be sure that the data is imported correctly (a part from running an application test i.e. by trial and error)?

You have the option of setting character sets/collations on the fly:

character-set-client
character-set-connection
character-set-filesystem
character-set-results
character-set-server
collation-connection
collation-database
collation-server

While trial-and-error may be necessary, don't go willy-nilly on these variables in /etc/my.cnf. You are better off setting them dynamically during any trial-and-error testing.

To make sure of any corner cases, look at the initial variables of any mysqldump and see if character sets or collations are set in the beginning and reset at the bottom.

In fact, here is a sample for a mysqldump:

/*!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 */;

See the variables

@OLD_CHARACTER_SET_CLIENT
@OLD_CHARACTER_SET_RESULTS
@OLD_COLLATION_CONNECTION
SET NAMES is hardwired to utf8

You could perhaps set these options

when calling the mysqldump
place these options in /etc/my.cnf under [mysqldump] group section
edit these values for existing mysqldumps using perl, awk, etc.


Permission issue on restore of the db from primary to secondary while restoring the User DB from primary to secondary replica through query in cmd prompt and got following error message

Msg 262, Level 14, State 1, CREATE DATABASE permission denied in database 'master'.
RESTORE LOG is terminating abnormally.Msg 3013, Level 16, State 1, RESTORE LOG is terminating abnormally.

provided sa permission on the user, but still it did not work. where should i need to look.

Permissions

from https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql

  If the database being restored does not exist, the user must have
  CREATE DATABASE permissions to be able to execute RESTORE. If the
  database exists, RESTORE permissions default to members of the
  sysadmin and dbcreator fixed server roles and the owner (dbo) of the
  database (for the FROM DATABASE_SNAPSHOT option, the database always
  exists). RESTORE permissions are given to roles in which membership
  information is always readily available to the server. Because fixed
  database role membership can be checked only when the database is
  accessible and undamaged, which is not always the case when RESTORE is
  executed, members of the db_owner fixed database role do not have
  RESTORE permissions.


Implement check constraint for varchar (7) to enforce YYYY-MM I am considering the following with regards to implementing a check constraint on a column I am calling year_month. My objective is to have a check constraint to enforce YYYY-MM.

My intention is to create the column with data type: varchar(7)
example value that would be saved from my front end: 2017-08

Would I then add a regular expression to enforce only numbers, dashes and max 7 in my check constraint setup? Or could I  add some sort of regular expression to check for this pattern more exactly?

You could try concatenating a valid 'day' on to the YYYY-MM values and use a CASE expression and the ISDATE function.

IF EXISTS (
        SELECT *
        FROM dbo.TEST
        )
    DROP TABLE dbo.TEST
go
CREATE TABLE Test (
    ID INTEGER
    ,YYYY_MM_CHAR CHAR(7) NOT NULL
    ,CONSTRAINT chk_YYYY_MM_CHAR CHECK (
        CASE
            WHEN ISDATE(YYYY_MM_CHAR + '-01') = 1
                THEN 1
            ELSE 0
            END = 1
        )
    );

INSERT INTO TEST (ID,YYYY_MM_CHAR) VALUES(1,'2014-01')
INSERT INTO TEST (ID,YYYY_MM_CHAR) VALUES(1,'2014-13')

(1 row(s) affected)
Msg 547, Level 16, State 0, Line 9
The INSERT statement conflicted with the CHECK constraint "chk_YYYY_MM_CHAR". The conflict occurred in database "master", table "dbo.Test", column 'YYYY_MM_CHAR'.
The statement has been terminated.


PostgreSQL - how to recognize if function was called by delete or update trigger? I have function, which should be called after update and delete on one table.

At the end of function I have to return "old" if function was called after delete or "new" if procedure was called after update.

I did it like this:

if new is null
then
  return old;
end if;

return new;

But I get error telling me that "new" has not been initialized if function is called after delete.
My question is how to recognize if function was called after delete or update?

Assuming this is a PL/pgSQL function, then - as documented in the manual - a trigger function automatically has access to some special variables. One of them is TG_OP that identifies the trigger operation.

The manual even has an example on how to use it:

IF (TG_OP = 'DELETE') THEN
  -- do something with the OLD record
ELSIF (TG_OP = 'UPDATE') THEN
  -- do something with the NEW record
ELSIF (TG_OP = 'INSERT') THEN
  -- do something entirely different
END IF;


Server audit with more than 1.000 entries On my Mssql 2008r2 I created a server audit for all login and logout:

USE MASTER
GO

CREATE SERVER AUDIT audit_test
TO FILE (FILEPATH = 'D:\Audit')
GO

CREATE SERVER AUDIT SPECIFICATION audit_test_spec
FOR SERVER AUDIT [audit_test]
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (LOGOUT_GROUP)
WITH (STATE=ON)
GO

For maximum file space I have set 1024 MB, more than enough.

However, when from the SQL Server Management Studio I go to security/audits/audit_test/view audit logs it only displays exactly the latest 1.000 rows. On the other hand, the file produced by the audit is 317 MB, so I guess it has more than 1.000 rows.

I even tried setting a filter, but it did not help: no line appear outside the latest 1.000.

How do I read the audit log file?

Have you tried reading your audit using sys.fn_get_audit_file?

-- query all audit files in the D:\audit directory
SELECT *
 FROM sys.fn_get_audit_file('D:\audit\*.sqlaudit', default, default)

The CREATE SERVER audit TSQL command you used did not specify

MAXSIZE = { max_size }
The default value is UNLIMITED.

MAX_ROLLOVER_FILES ={ integer | UNLIMITED}
The default value is UNLIMITED.

MAX_FILES =integer
Specifies the maximum number of audit files that can be created. Does not rollover to the first file when the limit is reached. When the MAX_FILES limit is reached, any action that causes additional audit events to be generated will fail with an error.

Not sure what the default is for MAX_FILES but its probably the FS limit which is somewhere around two billion.

So no there should not be any limit on the size of the audit files generated or the number of rows stored within them.


Allow db_owner to add/view existing logins to database On our development server (SQL 2008 R2) we want local dbo's to be able to add existing logins on the server to the database.  The problem is they can't view them, they can only see themselves in the list.  I know this was a security change in later versions of SQL Server to separate/restrict permissions more, but in our development environment it is just a pain.

I came across a post that suggested giving access to several server metadata views to the public role.  As this would make things more like SQL 2000.  (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=104997 which internally references http://technet.microsoft.com/en-us/magazine/cc161026.aspx)

GRANT VIEW ANY DEFINITION TO public
GRANT VIEW SERVER STATE TO public

But this seems like giving too much permissions over to the public role.  I just want to give the dbo's just enough rights to the system metadata views to allow them to browse for the existing logins on the server.  That way they can select who they need to add without having to message the server admins each time.

Does anyone know the minimum set of views that should be granted permissions in order to allow this?

Thanks!

Isn't GRANT VIEW DEFINITION ON LOGIN::Whoever TO [domain\dbogroup] in master enough?
This is implied by MSDN (I think, can't test) in GRANT Server Principal Permissions

Failing that, one quirk that may be useful for SQL Server 2005+

This may give no rows

SELECT * FROM sys.server_principals WHERE name = 'domain\somegroup';

But this will give a number if it exists

SELECT SUSER_ID('domain\somegroup');

Note: SQL Server 2012 has contained databases where logins are at the DB level


Understanding connection string syntax I am following How To Create a SQL Server Database Programmatically but find limited resources explaining the syntax is greater detail other than MSDN: Filegroup Options.

Take the following code on MSDN for example:

SqlConnection myConn =
new SqlConnection ("Server=localhost;Integrated security=SSPI;database=master");

str = "CREATE DATABASE MyDatabase ON PRIMARY " +
    "(NAME = MyDatabase_Data, " +
    "FILENAME = 'C:\\MyDatabaseData.mdf', " +
    "SIZE = 2MB, MAXSIZE = 10MB, FILEGROWTH = 10%) " +
    "LOG ON (NAME = MyDatabase_Log, " +
    "FILENAME = 'C:\\MyDatabaseLog.ldf', " +
    "SIZE = 1MB, " +
    "MAXSIZE = 5MB, " +
    "FILEGROWTH = 10%)"

Questions:

Back in SQL Server 2012, I would have in code Server=localhost\SQLExpress where Configuration Manager would show the instance as SQL Server (SQLExpress). Now with SQL Server 2016, I do not understand why it is simply Server=localhost because in Configuration Manager there is SQL Server (MSSQLServer) and I thought the syntax should be Server=localhost\MSSQLServer instead. Is it related to the Client Protocol? (but the Protocol did not differentiate the two version of SQL Server.
What is the difference between Database's name (MyDatabase) and NAME (MyDatabase_Data)? Why the need to assign a different name to a database which already has a name. What is the purpose?
How does the file grow in FILEGROWTH? Does it grow per SQL query? (For example I am now allowed to inserting large enough of textual data exceeding 1MB (10% of MAXSIZE) in one query) Or, does it mean the rate of growth (1MB per second)?

The reason that localhost is acceptable would be because it is using a default instance of SQL Server. The default instance, by name, would be MSSQLServer, however you do not need to provide that. Previously the express was a named instance, which requires you to pass along the instance name (or port) in order to connect.
The database name is MyDatabase. The assignment of MyDatabase_Data is for the data file inside the database. In this example a data and log file are designated and named. You can name these however you like (although you cannot have duplicate names within a database). You can also rename them after the database has been created should you so wish using an ALTER DATABASE statement.
Filegrowth is dependant upon data being written to the database. Log files will continue to grow if the database is in full or bulk-logged recovery unless you take log backups. However if in simple recovery then the log will grow as large as is required to support open transactions that happen, or to prevent a loss of data with CDC or replication (if a log continues to grow you can check for reasons using the log_reuse_desc column of sys.databases). For the data file, that will grow as you insert data, as an when needed. It is generally recommended to not use percentage based filegrowths as this can lead to unexpectedly large file growths, not to mention performance problems.

A further note to filegrowth. If you do not have IFI enabled then you will have IO stalls on writes to the data file that cause it to grow. Those stalls will last as long as it takes to grow out the file and zero it out to ensure the space is clear (2016 does not use zeroes, however the concept is the same). With IFI enabled (requires Perform Volume Maintenance Tasks permission for the service account, this can be done at install time with SQL 2016) then the data file can grow instantly and does not need zeroing out.
Log files do not have the option for IFI (for security and data safety reasons), so any growth on that file will cause IO stalls while that completes, impacting performance.

The MAXSIZE settings in the create database statement will prevent the files from growing beyond those values. This would mean that you could not store more an a total of 10MB of data (including system data), and that no individual transaction could be larger than 5MB in size. Both of these values can be adjusted using an ALTER DATABASE statement.


Fix MySQL server crash We recently suffered a pretty severe server crash on one of our linux machines (Fedora Core 5), which has resulted in a lot of corruption of data, unfortunately the mysql database is amongst the data which has been lost.
While we were able to get the server to boot, mysql still refuses to load. The server still produces a lot of errors on startup, so I have created a new server (Ubuntu 8.10) and copied the mysql directory across to this - but again mysql will not start.

Attached is a dump of the errors shown when running mysqld --verbose, with the innodb_force_recovery=6 option .

Sometimes while running this check, it will pass the sanity check, while other times it will fail.

I have found plenty of articles on how to recover from a crash, but all assume that you can actually get mysql to start - which is not the case here. Additionally i have tried all innodb_force_recovery options, from 1-8, and none of them allow mysql to start.

Can anyone advise on how to go about recovering the data? If I browse the ibdata1 file I can see most of the data (there appear to be a few rows missing). Are there any tools that can read the ibdata1 file and extract data row by row?

All help greatly appreciated - as I am starting to fail my sanity check!

Can anyone advise on how to go about recovering the data? If I browse the ibdata1 file I can see most of the data (there appear to be a few rows missing). Are there any tools that can read the ibdata1 file and extract data row by row?

Please don't do this... you may end up corrupting innodb data files.

It is better to follow what the error log says..

  "Please read http://dev.mysql.com/doc/mysql/en/using-stack-trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do
  resolve it"

Just follow the steps provided in the link. Once the stack trace issue is resolved you will have the exact cause for the problem and the solution.


Suppress repeating values I have a table that has columns like following. Batch number is a database design thing like row number

demand_id        batch_number    debit status     customer
     34               1             yes
     34               2                             jack
     35               1             no
     35               2                             kate

OUTPUT:

demand_id        batch_number    debit status     customer
     34               1             yes
                      2                             jack
     35               1             no
                      2                             kate

Here, I have tried and tested it and it gives the output you wanted (this was tested in Oracle):

SELECT
    CASE
        WHEN dr <> 1 THEN NULL
        ELSE demand_ind
    END demand_ind,
    batch_number,
    debit_status,
    customer
FROM (
SELECT
    demand_ind,
    batch_number,
    debit_status,
    customer,
    ROW_NUMBER() OVER(PARTITION BY demand_ind ORDER BY rowid) AS dr
FROM test2
) test3;


Working with multiple databases? Using sqlite3 on Linux, how can one work with multiple databases?

I'd like to do something like the following in database 1?

sqlite3 database1.db
insert into database1.mytable values (select * from database2.mytable)

How do I write the SELECT query for database2 above?

(reposting swasheck's comment as reply)

sqlite3 database1.db
> ATTACH DATABASE 'database2.db' AS database2;
> INSERT INTO mytable SELECT * FROM database2.mytable;


Can I change max_allowed_packet for a MySQL database, but not others in the same instance? We have a MySQL server with multiple databases in the same instance. Can I change the max_allowed_packet value for just one of the database?

From looking at this, it seems SET GLOBAL max_allowed_packet=xx will affect the entire server while SET max_allowed_packet=xx will only affect the current session

According to the MySQL Documentation on max_allowed_packet

  The maximum size of one packet or any generated/intermediate string.

  The packet message buffer is initialized to net_buffer_length bytes,
  but can grow up to max_allowed_packet bytes when needed. This value by
  default is small, to catch large (possibly incorrect) packets.

  You must increase this value if you are using large BLOB columns or
  long strings. It should be as big as the largest BLOB you want to use.
  The protocol limit for max_allowed_packet is 1GB. The value should be
  a multiple of 1024; nonmultiples are rounded down to the nearest
  multiple.

  When you change the message buffer size by changing the value of the
  max_allowed_packet variable, you should also change the buffer size on
  the client side if your client program permits it. The default
  max_allowed_packet value built in to the client library is 1GB, but
  individual client programs might override this. For example, mysql and
  mysqldump have defaults of 16MB and 24MB, respectively. They also
  enable you to change the client-side value by setting
  max_allowed_packet on the command line or in an option file.

Based on this, your only chance to set it for your connection would be when starting the client program:

mysql -Dmydb --max-allowed-packet=1073741824

After all

max_allowed_packet is an option for mysql client
max_allowed_packet is an option for mysqldump client

You cannot set it for the connection because it is a global option for how packets are used.

I just tried to run this in MySQL 5.6.15 for Windows and MySQL in CentOS release 6.5 (Final) and I got this message

mysql> set max_allowed_packet = 1024 * 1024 * 1024;
ERROR 1621 (HY000): SESSION variable 'max_allowed_packet' is read-only. Use SET GLOBAL to assign the value

Please read my posts on the MySQL Packet and its global effects

Apr 21, 2011 : What does the MySQL “max_allowed_packet” setting actually control?
Apr 27, 2011 : Changed max_allowed_packet and still receiving 'Packet Too Large' error
Aug 01, 2011 : How does max_allowed_packet affect the  backup and restore of a database?
Feb 16, 2012 : Does the max_allowed_packet variable have any effect on LOAD LOCAL INFILE?
Aug 14, 2012 : "Mysql has gone away" heavy multiple insert
Jul 03, 2013 : What max_allowed_packet is big enough, and why do I need to change it?
All my posts in the DBA StackExchange for max_allowed_packet


What would be the best way to show table/database growth? I've created a table that stores top 10 biggest tables on a database. it's schema is like this:
(example with 3 tables only)
| Table | size_mb | Date       |
|-------|---------|------------|
| tb1   | 2       | 01/01/2020 |
| tb2   | 5       | 01/01/2020 |
| tb3   | 1       | 01/01/2020 |
| tb1   | 4       | 02/01/2020 |
| tb2   | 12      | 02/01/2020 |
| tb3   | 2       | 02/01/2020 |
| tb1   | 10      | 03/01/2020 |
| tb2   | 20      | 03/01/2020 |
| tb3   | 5       | 03/01/2020 |

as our tables grow really fast, I have table size every day. in the example above, theres only the first day of the month, because this is what I'm trying to achieve with some kind of pivot or something.
What I would really want is to pivor this table by month.
I mean, I would like to have something like this:
| Table | size_JAN | size_FEV | size_MAR |
|-------|----------|----------|----------|
| tb1   | 2        | 5        | 6        |
| tb2   | 5        | 7        | 10       |
| tb3   | 1        | 2        | 6        |

I'm trying to think not about pivot itself but, if there's a good way to send a monthly report with this information, together with historical data.
EDIT:
the best I could do is to separate by month:
DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(Tabela)
                    from tbldatabasesize_tables
                    group by Tabela
                    order by Tabela
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')

set @query = 'SELECT Ano,
                    case when mes = 1 then ''Jan''
                         when mes = 2 then ''Fev''
                         when mes = 3 then ''Mar''
                         when mes = 4 then ''Abr''
                         when mes = 5 then ''Mai''
                         when mes = 6 then ''Jun''
                         when mes = 7 then ''Jul''
                         when mes = 8 then ''Ago''
                         when mes = 9 then ''Set''
                         when mes = 10 then ''Out''
                         when mes = 11 then ''Nov''
                         when mes = 12 then ''Dez''
end as Mes,' + @cols + ' from
             (
                select
                    year(DATE_REPORT) as Ano,
                    month(DATE_REPORT) as Mes,
                    Tabela,
                    convert(int,Tamanho_Da_Tabela_em_MB) as Tamanho_Da_Tabela_em_MB
                from tbldatabasesize_tables
            ) x
            pivot
            (
                avg(Tamanho_Da_Tabela_em_MB)
                for Tabela in (' + @cols + ')
            ) p '

execute(@query);

| Ano  | Mes | TB1| TB2 | TB3 | TB4 | TB5 | TB6 | TB7 | TB8 | TB9| TB10 |
|------|-----|-------------|-------------------|---------------------|----------------|------------|-----------|---------------|--------------|--------------------------------|------------------|
| 2020 | Mar | 3439        | 26363             | 77232               | 235643         | 32718      | 196380    | 11141         | 6570         | 9606                           | 24820            |
| 2020 | Abr | 3445        | 26413             | 77660               | 239613         | 32718      | 199713    | 11210         | 6591         | 9926                           | 24952            |
| 2020 | Mai | 3453        | 26493             | 78479               | 244071         | 32718      | 203283    | 11341         | 6613         | 10270                          | 25084            |
| 2020 | Jun | 3472        | 26583             | 79440               | 247916         | 32718      | 197185    | 11482         | 6630         | 10514                          | 25217            |

There's a hierarchy in analytics value that goes descriptive, diagnostic, predictive, prescriptive. What your question talks about is "descriptive." I think you should aim for "predictive."
You're collecting these numbers because you're concerned about exhausting some resource - disk space, backup size, sequential IDs, whatever. That's what will cause problems, not the absolute size of the table per se. Therefore that is what should be reported.
The output should be

Extrapolating over the last 4 months, Disk D: will be full by Jan 2021.
Last month's report predicted Feb 2021.

There are many ways to calculate the exhaustion point. Simple linear extrapolation is one. I've used Monte Carlo to some success.


Skip Immediate Next Cordinate I have a table foo where x, y represents coordinates
How do I discard the immediate next coordinate while using select?

  x   |  y | value
------+-----+-------
   32 | 10 |     7
   32 | 11 |     2
   32 | 15 |     2
   32 | 16 |     5
   32 | 21 |    25
   33 | 17 |    57
   33 | 21 |    83
   33 | 22 |    86

Output

  x   |  y | value
------+-----+-------
   32 | 10 |     7
   32 | 15 |     2
   32 | 21 |    25
   33 | 17 |    57
   33 | 21 |    83

Here cordinates (32,11), (32,16), (33,22) were skipped since they have an immediate previous coordinate (32,10) -> (32,11), (32,15) -> (32,16), (32,21) -> (33,22)

Is this possible using a query?
I am using Postgres

You want to filter out those rows for which a row with the same x and the previous y exists:

SELECT *
FROM my_table
WHERE NOT EXISTS (SELECT *
                  FROM my_table AS previous
                  WHERE (previous.x, previous.y )
                      = (my_table.x, my_table.y - 1));


Understanding joined set-returning functions in the FROM list I am using a construct like this very often:

SELECT *
FROM
    my_table,
    my_srf(my_column)

for example here:

CREATE TABLE my_table (
    my_col text[]
);

INSERT INTO my_table VALUES
('{a,b}'),
('{c,d,e}');

SELECT
    *
FROM
    my_table,
    unnest(my_col);

The result is as expected:

my_col    unnest
{a,b}     a
{a,b}     b
{c,d,e}   c
{c,d,e}   d
{c,d,e}   e

demo:db<>fiddle

Now I thought about this construct a little bit and got confused. The comma notation is the shortcut for as CROSS JOIN. In fact, this works as well:

SELECT
    *
FROM
    my_table
CROSS JOIN
    unnest(my_col)

But because this is a cross join, this result would look more logical to me:

my_col    unnest
{a,b}     a
{a,b}     b
{a,b}     c
{a,b}     d
{a,b}     e
{c,d,e}   a
{c,d,e}   b
{c,d,e}   c
{c,d,e}   d
{c,d,e}   e

A cross join joins all records from one part with all records of the other.

Although I am using this SRF joins as described above, I am now not sure anymore why it works in detail. So, I think that I obviously did not understand the complete functionality of SRFs so far.

Because the documentation made it not clearer to me, I would like to ask you to explain the behaviour.

The piece that you are missing is, that this is not a regular cross join, but a "LATERAL" cross join.

So the fully written equivalent is:

SELECT *
FROM my_table
   CROSS JOIN LATERAL unnest(my_col);

The keyword LATERAL is implicitly assumed if the join is done against a set returning function that's why cross join unnest(my_col) or even from my_table, unnest(my_col) works as well.

The LATERAL will change the cross join such that it takes one row from my_table and does a cross join of that (one) row with all rows returned from the set returning function. Then it does the same with the next row and so on.

So it generates the cross join between the row containing array {a,b} and all its elements. And then another cross join with the row containing the array {c,d,e} and all its elements.

The manual explains it this way:

  When a FROM item contains LATERAL cross-references, evaluation proceeds as follows: for each row of the FROM item providing the cross-referenced column(s), or set of rows of multiple FROM items providing the columns, the LATERAL item is evaluated using that row or row set's values of the columns. The resulting row(s) are joined as usual with the rows they were computed from. This is repeated for each row or set of rows from the column source table(s)


How to identify what is causing unusual high CPU load in MySQL? I have a MySQL 5.7 server that is since recently under very high CPU load, but no clue where this load comes from.

Looking at the MySQL processlist, it does not look like the server is doing much. There are some connections, but nothing heavy or special that could explain the high CPU.

In efforts to improve the situation I have been doing some data cleanup and applying some advice given by the mysqltuner script, but nothing seams to help much.

My last thought are that maybe something is damaged (some tables?) and MySQL is busy in the background, but I dunno if this makes any sense...

So my question is really, how to go about this? Looking at the processlist like everyone is saying, is not giving me the answers I need. Something inside MySQL is definately eating CPU behind the scenes.

PS: This server is quite powerful and there is plenty of RAM and CPU cores, so this is certainly not a hardware limit.

The slowlog.  Granted, this picks up on both high CPU and high I/O, but still it will give you what you are looking for.

Suggestions on digesting, etc: http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog

Often the 'cure' is an appropriate composite index.


With MS SQL Server are the generated constraint names predictable? When you create your primary key, and foreign key constraints in a CREATE TABLE script, there are named constraints that are created, such as FK__RecentlyVi__ScId__2764765D ... are these constraints predictable?  IE: if you run the same creation script on another server, will the constraint name be the same?

I ask because with entity framework, when you have multiple references to a secondary table, you get properties like...  Foreign, Foreign1, Foreign2, etc... and sometimes when re-generating the entity model the order is different...  I've come up with the following to work around this, but want to know if the "default" constraint names will work, even though I'm now using named constraints.

My workaround is included below.  If I need this again in the future, may refactor out getting the property of an entity based on the foreign key, and object type name.

private Contact GetContactMatchForForeignKey(string foreignKeyName)
{
  var props = typeof(Order).GetProperties().Where(prop => Attribute.IsDefined(prop, typeof(EdmRelationshipNavigationPropertyAttribute)));
  foreach (var prop in props) {
    var attrs = prop.GetCustomAttributes(typeof(EdmRelationshipNavigationPropertyAttribute), true);
    foreach (var attr in attrs) {
      var a = (EdmRelationshipNavigationPropertyAttribute)attr;
      if (a.RelationshipName == foreignKeyName && a.TargetRoleName == "Contact") {
        return (Contact)prop.GetValue(this, null);
      }
    }
  }
  return null;
}

private void SetContactMatchForForeignKey(string foreignKeyName, Contact value)
{
  var props = typeof(Contact).GetProperties().Where(prop => Attribute.IsDefined(prop, typeof(EdmRelationshipNavigationPropertyAttribute)));
  foreach (var prop in props) {
    var attrs = prop.GetCustomAttributes(typeof(EdmRelationshipNavigationPropertyAttribute), true);
    foreach (var attr in attrs) {
      var a = (EdmRelationshipNavigationPropertyAttribute)attr;
      if (a.RelationshipName == foreignKeyName && a.TargetRoleName == "Contact") {
        prop.SetValue(this, value, null);
        return;
      }
    }
  }
}

public Contact Purchaser
{
  get { return GetContactMatchForForeignKey("FK_..."); }
  set { SetContactMatchForForeignKey("FK_...",value); }
}

public Contact Seller
{
  get { return GetContactMatchForForeignKey("FK_..."); }
  set { SetContactMatchForForeignKey("FK_...",value); }
}

No, the constraint name is completely unpredictable. If you want your names to be consistent, you can name them correctly by applying a predictable / repeatable name manually. I have no idea how you would do this in the code you have, but in T-SQL instead of:

CREATE TABLE dbo.foo(bar INT PRIMARY KEY);

CREATE TABLE dbo.blat(bar INT FOREIGN KEY REFERENCES dbo.foo(bar));

(The above end up with constraints having names like PK__foo__DE90ECFF6CF25EF6 and FK__blat__bar__1B1EE1BE.)

You would say:

CREATE TABLE dbo.foo(bar INT, CONSTRAINT PK_foo PRIMARY KEY (bar));

CREATE TABLE dbo.blat(bar INT, CONSTRAINT fk_foobar FOREIGN KEY(bar)
  REFERENCES dbo.foo(bar));


Mongo: how to covert a string to decimal? I have written millions of documents with $CURRENT_CASH_BALANCE as strings instead of decimals.  How can I convert them all to decimals?

Consider a collection with documents:

{ _id: 1, bal: "123.45" }
{ _id: 2, bal: "67890"}
{ _id: 3, bal: 999 }

The following command can be run from the mongo shell to update the two documents with the field bal canverted from string data type to NumberDecimal:

db.collection.find( { bal: { $type: "string" } }
).forEach( doc => { doc.bal = NumberDecimal(doc.bal); db.collection.updateOne( { _id: doc._id }, { $set: { bal: doc.bal } } ); } )

After the update, the documents:

{ "_id" : 1, "bal" : NumberDecimal("123.45") }
{ "_id" : 2, "bal" : NumberDecimal("67890") }
{ "_id" : 3, "bal" : 999 }


Regarding configuration of kernel parameter Before installation of ORACLE, I have to set some kernel parameters such as

kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 6815744
fs.aio-max-nr = 1048576
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576

I've searched what they are, but it's quite hard to understand.

For example, I can get the information for kernel.shmmni, which is max number of segments. This was easy, but I can't understand what kernel.sem and net.core mean and why they are required.

Can someone please explain the meaning of these parameters?

The parameters are all prefixed according to which part of the system  they affect. fs = filesystem, net = networking etc.

kernel.shmmni = 4096 This sets the system wide maximum number of shared memory segments. At a high level, a share memory segment is a "piece" of memory that multiple processes can all attatch to at the same time. You can view all current shared memory segments on a Linux box using the command ipcs -m.

kernel.sem = 250 32000 100 128 configures semaphores. A semaphore is best described as:

  A value in a designated place in operating system (or kernel) storage
  that each process can check and then change. Depending on the value
  that is found, the process can use the resource or will find that it
  is already in use and must wait for some period before trying again.

This link
 explains how semaphores are used & what the 4 different values actually set.

fs.file-max = 6815744 sets the maximum number of files that can be open systemwide at any given point in time. Once the limit has been reached, trying to open another file will fail.

fs.aio-max-nr = 1048576 is (simplified for the sake of this answer) the maximum number of concurrent file read/write operations that can be in progress at any point in time.

net.ipv4.ip_local_port_range = 9000 65500 is the IP port range used for outgoing connections. So when a process opens a connection to the outside world, it'll use a port between 9000 and 65500. This explains what ports are.

The following are networking parameters, and basically control how much network data can be in flight at any point in time.

net.core.rmem_default = 262144 is the default OS receive buffer size for all types of connections.

net.core.rmem_max = 4194304 is the maximum OS receive buffer size for all types of connections.

net.core.wmem_default = 262144 is the default OS send buffer size for all types of connections.

net.core.wmem_max = 1048576 is the maximum OS send buffer size for all types of connections.


Monstrous query hanging on "copying to tmp table" in MySQL I have a huge query being run by one of my developers which seems to hang no matter which performance tunables I adjust.  Though I'm somewhat of novice at writing SQL queries, at first glance the query seems like it could use a lot of work.  I've already set tmpdir to a RAM disk, but this doesn't seem to help.  It would also appear that even though the mysql process state is in "copying to tmp table", nothing is actually being written there.  This is on an Intel Xeon L5520 (8-core machine w/HT) with 72GB RAM.

Here is the query:

http://pastebin.com/6mGUtncc

Here is an explain statement run on that query:

id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
1       PRIMARY <derived2>      ALL     NULL    NULL    NULL    NULL    397520  Using temporary; Using filesort
2       DERIVED <derived3>      ALL     NULL    NULL    NULL    NULL    397520  Using temporary; Using filesort
2       DERIVED o       eq_ref  PRIMARY,ndx_local_order_date,ndx_merchant_id    PRIMARY 4       order_stats.master_ticket_id    1       Using where
2       DERIVED m1      eq_ref  PRIMARY,fk_merchant3    PRIMARY 4       ticketing_brevard_prod.o.merchant_id    1       Using where
2       DERIVED oi      ref     order_items1    order_items1    4       order_stats.master_ticket_id    4
2       DERIVED th      ref     PRIMARY,ticket_header2  ticket_header2  4       order_stats.master_ticket_id    2       Using where
3       DERIVED m       index_merge     PRIMARY,fk_merchant3    PRIMARY,fk_merchant3    4,5     NULL    2       Using union(PRIMARY,fk_merchant3); Using where; Using index; Using temporary; Using filesort
3       DERIVED o2      ref     PRIMARY,ndx_order_date,ndx_merchant_id  ndx_merchant_id 4       ticketing_brevard_prod.m.merchant_id    40603   Using where
3       DERIVED th2     ref     ticket_header2,ticket_header3,fk_ticket_header7 ticket_header2  4       ticketing_brevard_prod.o2.master_ticket_id      2
3       DERIVED customer_type   eq_ref  PRIMARY PRIMARY 4       ticketing_brevard_prod.th2.customer_type        1
3       DERIVED package_header  eq_ref  PRIMARY PRIMARY 4       ticketing_brevard_prod.th2.package_id   1
4       DEPENDENT SUBQUERY      mpc     eq_ref  PRIMARY,merchant_package_characs2,merchant_package_characs3     PRIMARY 60      ticketing_brevard_prod.th2.merchant_id,ticketing_brevard_prod.th2.package_id    1       Using where

I think it's clear to see from the EXPLAIN output that copying 397520 rows of data to tmp is the likely culprit, however I'm not sure what to suggest to the dev who wrote query on how to improve it.  It'd be great if someone could point me in the right direction.  Thanks in advance!

SHOW VARIABLES LIKE '%buffer%';

Variable_name   Value
bulk_insert_buffer_size 8388608
innodb_buffer_pool_shm_checksum ON
innodb_buffer_pool_shm_key      0
innodb_buffer_pool_size 60129542144
innodb_change_buffering inserts
innodb_log_buffer_size  4194304
join_buffer_size        16777216
key_buffer_size 67108864
myisam_sort_buffer_size 134217728
net_buffer_length       16384
preload_buffer_size     32768
read_buffer_size        16777216
read_rnd_buffer_size    33554432
sort_buffer_size        1073741824
sql_buffer_result       OFF

Wow, that is a doozie of a query. At this point it has little to do with your configuration variables and you should focus on how to optimize the query.

It is tough to give you a magic bullet, though, without knowing what question you are trying to determine from this monstrous query. At first glance, it appears it is trying to do too much.

I would start by removing these two problems from your EXPLAIN plan:

1       PRIMARY <derived2>      ALL     NULL    NULL    NULL    NULL    397520  Using temporary; Using filesort
2       DERIVED <derived3>      ALL     NULL    NULL    NULL    NULL    397520  Using temporary; Using filesort

The first I think is the query for 'order_stats' derived table, the second I am assuming is the 'main' derived table, though it might be flipped. Regardless, I would start at the 'order_stats' (leave a comment with the pastebin of the explain and running time of non-explain, please).

Depending on what this query is trying to accomplish, I would recommend storing the results in a temporary table to be used in the join. One question I would like to know from your developer is, what is the english of the package_tickets and order_tickets computed columns. And if it could be accomplished another way if it turns out to be the bottleneck.


Replication master binlog rotation when network is unavailable I recently experienced an issue where the binlog file in master rotated because network connectivity between the slave and master was unavailable.

After solving the network issue, the slave was not able to follow the master as it was doing through previous binlog and position.

That was solved by purging the binlog to last binlog in master and pointing the slave to that last binlog and previous binlog's position which was following.

I am wondering if this issue is normal?

Whenever a Slave loses network connectivity, the IO thread simply dies and tries to reconnect every 60 seconds. You did the right thing in pointing the Slave back but let's clarify what to use to point back with.

For the give SHOW SLAVE STATUS\G

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.17.20.102
                  Master_User: replicant
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.002814
          Read_Master_Log_Pos: 823078734
               Relay_Log_File: relay-bin.007364
                Relay_Log_Pos: 823078879
        Relay_Master_Log_File: mysql-bin.002814
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 823078734
              Relay_Log_Space: 823079071
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
1 row in set (0.00 sec)

WHAT YOU SHOULD NOT PICK

Master_Log_File represents the log file containing the last successfully executed SQL statement on the Master that was recorded in the Slave's Relay Logs.
Read_Master_Log_Pos represents the position within Master_Log_File of the last successfully executed SQL statement on the Master that was recorded in the Slave's Relay Logs.

WHAT YOU SHOULD PICK

Relay_Master_Log_File represents the log file containing the last successfully executed SQL statement on the Master that was executed on the Slave.
Exec_Master_Log_Pos represents the position within Relay_Master_Log_File of the last successfully executed SQL statement on the Master that was executed on the Slave.

In most cases, when caught early enough, Master_Log_File and Relay_Master_Log_File are the same. When there is significant replication lag or if the SQL thread breaks and the IO Thread keeps collecting, Master_Log_File and Relay_Master_Log_File will be different.

CONCLUSION

ALWAYS PICK Relay_Master_Log_File and Exec_Master_Log_Pos as a restart point.

Here are my other posts that reemphasize this paradigm:

Dec 05, 2011 : Does `Seconds_Behind_Master` show exact Slave Lag from Master?
Jan 04, 2012 : MySQL Slave replication: Need to determine where replication left off to start slave in correct location
Jan 24, 2012 : Mysql master-master replication auto flush old logs (Under Reason #3)
Apr 04, 2012 : Seconds Behind Master in Cloud Service

CAVEAT #1

Please do not be concerned with binlog rotation on the Master when it comes to Replication's status. The Slave's IO Thread is very sensitive to the Master's change. Here is how you can verify this:

STEP 01 : On the Slave, run SHOW SLAVE STATUS\G

STEP 02 : On the Master

For MySQL 5.5, run FLUSH LOGS; or FLUSH BINARY LOGS;
Prior to MySQL 5.5, run FLUSH LOGS;

STEP 03 : On the Slave, run SHOW SLAVE STATUS\G

You will see the Master_Log_File and Read_Master_Log_Pos change immedaitely.

CAVEAT #2

Since Relay_Master_Log_File has the most recent binary from the Master to be executed on the Slave, that's the log you keep on the Master. From the above SHOW SLAVE STATUS\G, you would run

PURGE BINARY LOGS TO 'mysql-bin.002814';


convert default charset utf8 tables to utf8mb4 mysql 5.7.17 I tried the following steps to upgrade CHARSET to utf8mb4 and to connect it to the existing backend server.

ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
update the DB connection parameters.

"user:pass@tcp(server:3306)/mydbcharset=utf8mb4&collation=utf8mb4_unicode_ci"

This is the users table schema before migration.

CREATE TABLE IF NOT EXISTS `users` (
    `id`               BIGINT(20) NOT NULL AUTO_INCREMENT,
    `email`            VARCHAR(255) NOT NULL DEFAULT '',
    `password`         VARCHAR(255) NOT NULL DEFAULT '',
    `country_code`     CHAR(4) NOT NULL DEFAULT '',
    `industry`         VARCHAR(255) NOT NULL DEFAULT '',
    `company_size`     VARCHAR(255) NOT NULL DEFAULT '',
    `company_name`     VARCHAR(255) NOT NULL DEFAULT '',
    `last_name`        VARCHAR(255) NOT NULL DEFAULT '',
    `first_name`       VARCHAR(255) NOT NULL DEFAULT '',
    `newsletter`       TINYINT(1) NOT NULL DEFAULT 0,
    `company_website`  VARCHAR(255) NOT NULL DEFAULT '',
    `job_title`        VARCHAR(255) NOT NULL DEFAULT '',
    `profile_picture`  VARCHAR(255) NOT NULL DEFAULT '',
    `verified`         TINYINT(1) NOT NULL DEFAULT 0,
    `p_updated_time`   INT NOT NULL DEFAULT 0,
    `created`          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated`          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`email`)
)ENGINE = InnoDB DEFAULT CHARSET=UTF8;

Everything works fine so far.

My worry is that some of the VARCHAR columns have character constrain as 255. In this article https://mathiasbynens.be/notes/mysql-utf8mb4 and the accepted answer here How to easily convert utf8 tables to utf8mb4 in MySQL 5.5 says updating varchar(255) to 191 is needed. However I did not encounter issues without doing that, existing DB has around 100 user records and a dry run of the above migration worked perfectly.

Question are

Is it mandatory to check and update column lengths when moving from
utf8 to utf8mb4, if not what kind of issues could arise?
Is it also mandatory to change the CHARSET of the database? Why not change only the charset of the table?

--UPDATE--

For someone who runs into foreign key violations can use the below

SET FOREIGN_KEY_CHECKS=0;

-- Insert your other SQL Queries here...
 ALTER TABLE tble_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS=1;

5.5 and 5.6 had the 191 problem.  5.7 solved the problem.

See this for discussion of multiple workarounds in 5.5/5.6. Both articles you reference is dated, since they discusses 5.5.  (Note that I have already commented on both.)

I recommend you change 255 to some reasonable limit for each column, anyway.

A caveat with ALTER .. CONVERT TO .. -- If country_code were what it should be, namely CHARACTER SET ascii; the ALTER would change it to utf8mb4.

Q2...

The CHARSET of a column matters.  The CHARSET of a table is just the default for the columns.  Ditto for the database and any newly created table.

I prefer not to depend on such defaulting, and I explicitly state the CHARSET on any table I CREATE.  Others may prefer to do a ALTER DATABASE.


Can't get access to other database I am working on a project for my university. They gave me a login username and password for MySQL database they've setup on the server.

With that username and password, I'm able to login to the server. I ran the following query on it:

SHOW GRANTS;

And got following output

GRANT USAGE ON *.* TO 'me'@'localhost' IDENTIFIED BY PASSWORD '*mypass'

Now if I am trying to create a database with following code

CREATE DATABASE test;

I am getting following error

Error: 1044
Access denied for user 'me'@'localhost' to database 'test'

As far as I know, . means access to all tables of all databases. Where is the problem. Please help me to rectify me.

Please RUN the following:

select user();
select current_user();

The former is who you are attempting to login as; the second is what the database sees as connected. You will need to run grant_priv to user.

Here is how to verify:

mysql> SELECT host,user,password,Grant_priv,Super_priv FROM mysql.user

If the grant_priv is set to 'N' for your user, this needs to be 'Y'.

To grant access you can run:

UPDATE mysql.user
SET Grant_priv='Y', Super_priv='Y'
WHERE User='me'@'localhost';
FLUSH PRIVILEGES;
GRANT ALL ON *.* TO 'me'@'localhost'


Demystification of SQL Server optimization process We would like to see all variants of query plan considered during a query optimization by a SQL Server optimizer. SQL Server offers quite detailed insight using querytraceon options. For example QUERYTRACEON 3604, QUERYTRACEON 8615 allows us to print out MEMO structure and QUERYTRACEON 3604, QUERYTRACEON 8619 print out a list of transformation rules applied during the optimization process. That is great, however, we have several problems with trace outputs:

It seems that the MEMO structure contains only final variants of the query plan or variants that were later rewritten into the final one. Is there a way to find "unsuccessful/unpromising" query plans?
The operators in MEMO do not contain a reference to SQL parts. For example, LogOp_Get operator does not contain a reference to a specific Table.
The transformation rules do not contain a precise reference to MEMO operators, therefore, we can not be sure which operators were transformed by the transformation rule.

Let me show it on a more elaborated example. Let me have two artificial tables A and B:
WITH x AS (
        SELECT n FROM
        (
            VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)
        ) v(n)
    ),
    t1 AS
    (
        SELECT ones.n + 10 * tens.n + 100 * hundreds.n + 1000 * thousands.n + 10000 * tenthousands.n + 100000 * hundredthousands.n as id
        FROM x ones, x tens, x hundreds, x thousands, x tenthousands, x hundredthousands
    )
SELECT
    CAST(id AS INT) id,
    CAST(id % 9173 AS int) fkb,
    CAST(id % 911 AS int) search,
    LEFT('Value ' + CAST(id AS VARCHAR) + ' ' + REPLICATE('*', 1000), 1000) AS padding
INTO A
FROM t1;

WITH x AS (
        SELECT n FROM
        (
            VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)
        ) v(n)
    ),
    t1 AS
    (
        SELECT ones.n + 10 * tens.n + 100 * hundreds.n + 1000 * thousands.n AS id
        FROM x ones, x tens, x hundreds, x thousands
    )
SELECT
    CAST(id AS INT) id,
    CAST(id % 901 AS INT) search,
    LEFT('Value ' + CAST(id AS VARCHAR) + ' ' + REPLICATE('*', 1000), 1000) AS padding
INTO B
FROM t1;

Right now, I run one simple query
SELECT a1.id, a1.fkb, a1.search, a1.padding
FROM A a1 JOIN A a2 ON a1.fkb = a2.id
WHERE a1.search = 497 AND a2.search = 1
OPTION(RECOMPILE,
    MAXDOP 1,
    QUERYTRACEON 3604,
    QUERYTRACEON 8615)

I get quite complex output that describes MEMO structure (you may try by yourself) having 15 groups. Here is the picture, that visualizes MEMO structure using a tree.

From the tree one may observe that there were certain rules applied before the optimizer found the final query plan. For example join commute (JoinCommute), join to hash join (JNtoHS), or Enforce sort (EnforceSort). As mentioned it is possible to print out the whole set of rewriting rules applied by the optimizer using QUERYTRACEON 3604, QUERYTRACEON 8619 options.
The problems:

We may find JNtoSM (Join to sort merge) rewriting rule in the 8619 list, however, the sort-merge operator is not in MEMO structure. I understand that the sort-merge was probably more costly, but why it is not in MEMO?
How to know whether LogOp_Get operator in MEMO references to table A or table B?
If I see rule GetToIdxScan - Get -> IdxScan in the 8619 list, how to map it to the MEMO operators?

There is a limited number of resources about this. I have read many of the Paul White blog posts about transformation rules and MEMO, however, the above questions remain unanswered. Thanks for any help.

I will try to reply to your questions:
1. It seems that the MEMO structure contains only final variants of the query plan or variants that were later rewritten into the final one. Is there a way to find "unsuccessful/unpromising" query plans?
No, sadly there is no way to do that. @Ronaldo pasted a nice link in the comment. My suggestion is to use the Include Live Query Statistics

and try to find out if you see different query plan. Use top 10, top 1000, or * and you will see that different query plans will be proposed. You can also use query hint and force your query plan to a different pattern. Basically "do your own discarded query plan"
2. The operators in MEMO do not contain a reference to SQL parts. For example, LogOp_Get operator does not contain a reference to a specific Table.
Use QUERYTRACEON 8605, I can see a reference to the table:

3. The transformation rules do not contain a precise reference to MEMO operators, therefore, we can not be sure which operators were transformed by the transformation rule
I don't see any GetToIdxScan - Get -> IdxScan in the query you provided. My suggestion is to use Use QUERYTRACEON 8605, or QUERYTRACEON 8606, there should be a reference there.
EDIT:
So "... is it possible to see more information about the candidate plans in SQL Server."
The reply is no, because there are no other candidate query plan. In fact, is a common misconception that SQL Server returns you The Best query plan. SQL Server simply cannot calculate for you all possible solutions: that would take... I don't know... minutes...? hours...? Calculating every single solution is unfeasible.
But if you want to investigate why your query plan choose that pattern you can use:

SET SHOWPLAN_ALL ON : and SQL Server will return you a tree of the logic of every single calculation of your query plan

DBCC SHOW_STATISTICS('A', 'PK_A') : which will show you the statistics about a target table and constraint. I created a key to show you the results, naturally you will see more info if your table is queried more often

USE HINT('force_legacy_cardinality_estimation') : will allow you to use the former cardinality estimation, so you can check if you query plan might have been faster with the legacy cardinality estimation .


Oracle extract slow function call from a WHERE clause I have an oracle table in which dates are stored as the number of minutes since a specific date. It looks something like this:

CREATE TABLE MY_TABLE
(
  SOMETHING     NUMBER(15,1) NOT NULL,
  START_MINUTE  NUMBER(15,1) NOT NULL,
  STOP_MINUTE   NUMBER(15,1) NOT NULL
);

I've written two functions to convert between what are known as "MINUTE"s and the actual date/times that they represent. They look like this:

CREATE OR REPLACE FUNCTION FROM_MINUTE (MINUTE_IN IN NUMBER)
RETURN DATE AS
BEGIN
  /* Minute 0 = 12/30/1899 12:00am */
  RETURN (TO_DATE('1899-12-30', 'YYYY-MM-DD') + (MINUTE_IN / 1440));
END FROM_MINUTE;

CREATE OR REPLACE FUNCTION TO_MINUTE (DATE_IN IN DATE)
RETURN NUMBER AS
BEGIN
  /* Minute 0 = 12/30/1899 12:00am */
  RETURN
    (TRUNC(DATE_IN, 'DD') - TO_DATE('12/30/1899', 'MM/DD/YYYY')) * 1440 +
    TO_NUMBER(TO_CHAR(DATE_IN, 'HH24'))  * 60 +
    TO_NUMBER(TO_CHAR(DATE_IN, 'MI'));
END TO_MINUTE;

Now I'm trying to write a query that returns all rows that overlap with a specific time frame. It's taking quite a long time to run, presumably because of the function calls. I've tried several different versions of it:

-- This compares dates by converting the minutes to dates first
SELECT * FROM MY_TABLE
WHERE FROM_MINUTE(START_MINUTE) < TO_DATE('2013-01-31', 'YYYY-MM-DD')
AND FROM_MINUTE(STOP_MINUTE) > TO_DATE('2013-01-01', 'YYYY-MM-DD');

-- This compares minutes by converting the dates to minutes first
SELECT * FROM MY_TABLE
WHERE START_MINUTE < TO_MINUTE(TO_DATE('2013-01-31', 'YYYY-MM-DD'))
AND STOP_MINUTE > TO_MINUTE(TO_DATE('2013-01-01', 'YYYY-MM-DD'));

-- Finally I just got the minute values in a separate query...
SELECT
  TO_MINUTE(TO_DATE('2013-01-01', 'YYYY-MM-DD')) AS EARLIEST, -- Returns 59436000
  TO_MINUTE(TO_DATE('2013-01-31', 'YYYY-MM-DD')) AS LATEST    -- Returns 59479200
FROM DUAL;
-- and I plugged them directly into the query
SELECT * FROM MY_TABLE
WHERE START_MINUTE < 59479200
AND STOP_MINUTE > 59436000;

I'm pretty sure the problem is that the comparisons in the first two queries require a function call on every row to do a proper comparison. That's why the final query is so much faster, since it's just a direct comparison.

I would prefer to have a single query that runs as fast as the two parter above. Is there a way to force a query to run the function call just once and use the returned value for every subsequent comparison without having to be called for each row? I tried something like the following, but it didn't seem to work like I'd hoped:

SELECT * FROM MY_TABLE,
  (SELECT
    TO_MINUTE(TO_DATE('2013-01-01', 'YYYY-MM-DD')) AS EARLIEST, -- Returns 59436000
    TO_MINUTE(TO_DATE('2013-01-31', 'YYYY-MM-DD')) AS LATEST    -- Returns 59479200
  FROM DUAL) MY_MINUTES
WHERE START_MINUTE < MY_MINUES.LATEST
AND STOP_MINUTE > MY_MINUTES.EARLIEST;

The second form of your query will work if you make your mark your function as deterministic, meaning that for a given set of input values, it will always return the same result.
With that set, Oracle will only run the conversion once for each parameters in the where clause rather than for every row.

With this:

CREATE OR REPLACE FUNCTION TO_MINUTE_D (DATE_IN IN DATE)
RETURN NUMBER DETERMINISTIC AS
BEGIN
  /* Minute 0 = 12/30/1899 12:00am */
  RETURN
    (TRUNC(DATE_IN, 'DD') - TO_DATE('12/30/1899', 'MM/DD/YYYY')) * 1440 +
    TO_NUMBER(TO_CHAR(DATE_IN, 'HH24'))  * 60 +
    TO_NUMBER(TO_CHAR(DATE_IN, 'MI'));
END TO_MINUTE_D;
/

On a table filled with a large bunch of dummy rows (increasing ints), I get the following timings consistently:

SQL> SELECT * FROM MY_TABLE
WHERE START_MINUTE < TO_MINUTE(TO_DATE('2013-01-31', 'YYYY-MM-DD'))
AND STOP_MINUTE > TO_MINUTE(TO_DATE('2013-01-01', 'YYYY-MM-DD'));

no rows selected

Elapsed: 00:00:12.69

versus deterministic-annotated function:

SQL> SELECT * FROM MY_TABLE
     WHERE START_MINUTE < TO_MINUTE_D(TO_DATE('2013-01-31', 'YYYY-MM-DD'))
     AND STOP_MINUTE > TO_MINUTE_D(TO_DATE('2013-01-01', 'YYYY-MM-DD'));

no rows selected

Elapsed: 00:00:00.07

You should get very close to what you have with the values plugged in directly, and indexes on those columns can be used as if you'd plugged in literals.

(Putting the conversion function on the start|stop_minute columns isn't a good idea in general as you've discovered, unless you have a function-based index on those that matches exactly.)


How can I merge data from an Excel file into an access database with multiple tables? I have an access database with many internal tables.
Each table is a category of electronic components (resistor, capacitor etc)
In all tables, there is a manufacturer part number and a currently empty field for our internal part number.

I have an excel file containing the internal part numbers and the matching manufacturers part numbers.

How can I fill in the internal part number field for all tables from my excel file, matching on the manufacturer part number?

I'm sure this is simple, but I don't use access much, and I don't speak "accesseze".

To update your tables as described requires multiple steps that can't be answered in detail for this single question.  I'll give what I hope is a useful outline, but you will need to search for more specific help for each step (in this forum, on Stack Overflow, or on the web).

I am using Access 2013, but these steps should be identical/similar for versions back to Access 2007.

Import or Link to the Excel file:

In Access, go to the External Data ribbon (i.e. toolbar tab).
Select the Import Access Database icon.
Follow the import wizard steps.

Linking to the Excel file creates a link "table" in Access.  On the surface it behaves and looks like another Access table, but it reads the data directly from Excel. Whether you link to the file or import the data depends on your preference and use.  If the Excel file data changes often AND the file location and format remain the same, then linking can avoid having to re-import into a new table, handling duplicates, etc.
However, if the Excel file is not formatted simply (i.e. basic columns with headers) or there are other complications, importing the data into a standard Access table can avoid some headaches.

Write multiple update queries that joins a table to the Excel data and updates the part number.

Because you do not speak "accesseze", perhaps you also don't know SQL.  In that case, you can use the Query wizards in Access to accomplish the updates.  However, it is much more convenient to share queries using SQL in these forums, hence the following example SQL.  The convenient thing about Access is that it allows switching between SQL View and the visual Design View
The following example will of course only update records with exact matches.  It is prudent to write data validation queries which look for missing part numbers, ill-formed part numbers, etc.  Such queries will ultimately require other types of joins between the tables, looking for Null values, etc.
Example SQL with made-up names since your question didn't include more details about the data:

The WHERE Component_Table.InternalPartNo Is Null clause is there to avoid inadvertently overwriting existing data.  Only remove this criteria if you are certain that you want to overwrite existing numbers, but refer back to my previous hint about data validation.

UPDATE Component_Table
INNER JOIN Excel_Data ON Component_Table.ManufacturerID = Excel_Data.ManufacturerID
SET Component_Table.InternalPartNo = Excel_Data.InternalPartNo
WHERE Component_Table.InternalPartNo Is Null


Oracle: Full database (user) restore requires drop? In our development environment (Oracle 11g), we often have to restore databases (users) to previous versions to test upgrades, verify defects, etc.

We are using expdp/impdp to perform our backup/restores.

For back-ups we run...

expdp 'sys/password@orcl as sysdba' schemas=CurrentUser directory=DirLabel Dumpfile=MyDatabase.dmp logfile=MyDatabase.log

For restores we have been dropping the user (database) manually, and then running

impdp 'sys/password@orcl as sysdba' directory=DirLabel Dumpfile=MyOldDatabase.dmp logfile=MyRestore.log remap_schema=OLDUser:CurrentUser

We are now trying to automate this process and have a question.

Do we have to drop the current user (database) prior to the restore or is there some option/flag on impdb that can be used to force a complete restore?  I looked at TABLE_EXISTS_ACTION=REPLACE, but I noticed I still get the same "errors" during restore if I leave the option off the command-line.

Thoughts?

I haven't found any impdp commands that would allow you to drop existing schema prior to importing data. But you should know that Data Pump command-line utilities are based around PL/SQL packages DBMS_DATAPUMP and DBMS_METADATA. Thus you may well write your own PL/SQL scripts to automate your data move calling programs in DBMS_DATAPUMP directly. And since this is PL/SQL, you may just invoke DROP USER statement in your scripts prior to calls to programs in DBMS_DATAPUMP and DBMS_METADATA.

I think this approach is more flexible, and to further automate your tasks you can employ Oracle Scheduler.


What is the SQL Azure equivalent of the server "public" role? In our on-premise database, we create database users mapped to a login, and then assign the user to specific roles within the database.  Access to the database is strictly governed by which stored procedures each role has EXECUTE permissions on.  The user has no other rights other than by stored procedure grants (e.g. they cannot run arbitrary select statements in SSMS).

I notice that SQL Server (on-premise) provides a default user assignment to the server's "public" role.  When assigning user access to a SQL Azure server however, I do not see the "public" role.  However, I must select a role in order to create the user:

Can anyone please advise what I should do here to honour our existing approach, or whether the on-premise approach is even still possible?

I cannot find any documentation to describe what privileges are granted from the roles listed.

After much experimentation, I stumbled upon an answer on the Secure your Azure SQL Database.

Please correct me if I am wrong here!

It is strange that the documentation on securing SQL Azure does not mention the following in any detail.  The clues were here (emphasis mine):

  SQL Authentication, which uses username and password for logins and
  users that are valid only in the context of a specific database within
  a logical server.

  -snip-

  By default, the user can connect to the database, but has no
  permissions to read or write data.

In SQL Azure, it is possible to have database-contained users with a password (which is different from the on-premise approach of creating a user against an existing login account).  This makes sense, because the all-in-one contained approach allows for a seamless failover.

The solution is to use SSMS and not the Azure portal:

-- After connecting to SQL Azure within SSMS (as dbo)
CREATE USER [UserNameForApplication] WITH PASSWORD=N'543rg.....ehgdGDF';
GO
ALTER ROLE [MyExistingDatabaseRole] ADD MEMBER UserNameForApplication;
GO

My application can now access the SQL Azure database with zero additional rights that would otherwise be granted via the Azure portal.


Should a log table get an id field or primary key? I have a log table that captures the datetime stamp of when certain files were exported to another system.

The exportedLog table currently has three fields:

id                (primary key)
messageId         (int)
exportedDateTime  (datetime)

Reviewing this I found that the id field serves no purpose, as there are no joins to this table.
The only thing working on this table is the insert of the batch job that processes the messages and inserts into this log table.

Should I remove the id field?

Should I have a primary key on either messageId or exportedDateTime or both?

Should I remove the id field?

I would recommend keeping it.

You may not need the field now, but in the future, it can really help you out -- what if you need to store details of the files for each log entry?

I don't know how large this table will get and how quickly, but adding a column to a large table is typically an expensive operation. If the table is relatively small, then it's not a big deal to keep in terms of storage space. IMO, keep the column and save a potential headache later.

  Should I have a primary key on either messageId or exportedDateTime or both?

It doesn't sound like messageId alone would be unique in this table (though I could be wrong), and creating uniqueness on a date/time column alone can potentially create duplicates (and hence errors). The only option left is a 2-column key, which is not particularly appealing given the scenario I set out above.

Essentially, my point of this answer is that keeping the column is not a big deal (I'm assuming), but needing it later may be a big deal and/or require extra work to put it back.


Query returning data from two rows of a table in a single row My table looks like this:

ID     APPLICATION    OTHERVALUE
0001   subapp         SubvalueA
0001   mainapp        MainValue1
0002   subapp         SubvalueB
0002   mainapp        MainValue1
0003   subapp         SubvalueA
0003   mainapp        MainValue2

I need all lines of subapp, but then I also need value of OtherValue when the application is mainapp from same table where ID is the same. I'm using Oracle.

How can I build a query for this?

Expected result:

ID     APPLICATION    OTHERVALUE    OTHERVALUE2
0001   subapp         SubvalueA     MainValue1
0002   subapp         SubvalueB     MainValue1
0003   subapp         SubvalueA     MainValue2

You can use an aggregate function and a CASE statement to get the data into this format:

select id,
  max(case when application ='subapp' then application end) application,
  max(case when application ='subapp' then othervalue end) othervalue,
  max(case when application ='mainapp' then othervalue end) othervalue2
from yourtable
group by id;

See SQL Fiddle with demo

Result:

| ID | APPLICATION | OTHERVALUE | OTHERVALUE2 |
-----------------------------------------------
|  1 |      subapp |  SubvalueA |  MainValue1 |
|  2 |      subapp |  SubvalueB |  MainValue1 |
|  3 |      subapp |  SubvalueA |  MainValue2 |


MariaDB subqueries to same table and column resulting in several columns I have a table and want to pick monthly minute data to compare column wize in 10.3.13-MariaDB
Tested and tested for hours and hours different approaches without success, one example is below. Some tests does not complain syntactically but takes forever, and some tests complains about column names not recognized. All subqueries if tested separately return the same number of records, each in one column.
SELECT RD, OT1, OT2, OT3 FROM
(SELECT rdate from  OO where month(rdate) = 7 and year(rdate) = 2006) AS RD,
(SELECT ot from OO where month(rdate)=7 and year(rdate) =2006) AS OT1,
(SELECT ot from OO where month(rdate)=7 and year(rdate) =2007) AS OT2,
(SELECT ot from OO where month(rdate)=7 and year(rdate) =2008) AS OT3;`

The result should be something like:
RD                   OT1    OT2    OT3
2006-07-01 00:00:00  1.2345 2.1234 1.543
...                  ...    ...    ...
2006-07-31 23:59:00  3.456  3.234  2.234

And, no I dont want to use UNION because then they will still follow one after the other...
Any thoughts?!

You are pivoting too soon.  Do the union, then pivot the results.


MS SQL - CLR Stored Procedure runs as deleted user I have a SQL Server 2017 database which contains some CLR stored procedures. When trying to call those CLR SP's, I get a "login failed" error back from the CLR SP.

My user is connected and works fine - it seems that the CLR SP is coded to run as another specific user.

I don't have access to the underlying code for the CLR SP's. Is there any way for me to change which user it's executing as?

Assuming that the SQLCLR method truly is trying to log into SQL Server as a non-existing login ("user" is database-level), then that should only be due to using a regular / external connection (instead of the internal "context connection") and specifying a SQL Server login and password (instead of using a trusted connection / integrated security).
{ still, having the exact error message is always helpful :-) }

In terms of keeping the same process, you have two main options:

Re-compile the code with a new login and/or password:

There is a slight chance that the source code is in sys.assembly_files (it would be a row where file_id <> 1), but that practice was done (at least by default) in earlier versions of Visual Studio / SSDT and you don't really see it that much anymore.
Assuming that the source code isn't in that system view, you would need to export the actual assembly (same view, but this time you do want WHERE [file_id] = 1) and then you can use a disassembler to get the source code.

A much easier path should be to just re-add that deleted login. Is there a reason not to? If you aren't sure what password is being used, the two options noted above for getting the source code would also be how you find the password.


MySQL Set UTC time as default timestamp How do I set a timestamp column whose default value is the current UTC time?

MySQL uses UTC_TIMESTAMP() function for UTC timestamp:

mysql> SELECT UTC_TIMESTAMP();
+---------------------+
| UTC_TIMESTAMP()     |
+---------------------+
| 2012-07-01 11:36:35 |
+---------------------+
1 row in set (0.00 sec)

So I've tried:

CREATE TABLE `blah` (
`creation_time` TIMESTAMP DEFAULT UTC_TIMESTAMP,
...

And other variations, like UTC_TIMESTAMP(), but without success.

To go along with @ypercube's comment that CURRENT_TIMESTAMP is stored as UTC but retrieved as the current timezone, you can affect your server's timezone setting with the --default_time_zone option for retrieval. This allows your retrieval to always be in UTC.

By default, the option is 'SYSTEM' which is how your system time zone is set (which may or may not be UTC!):

mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM             | SYSTEM              |
+--------------------+---------------------+
1 row in set (0.00 sec)

mysql> SELECT CURRENT_TIMESTAMP();
+---------------------+
| CURRENT_TIMESTAMP() |
+---------------------+
| 2012-09-25 16:28:45 |
+---------------------+
1 row in set (0.00 sec)

You can set this dynamically:

mysql> SET @@session.time_zone='+00:00';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM             | +00:00              |
+--------------------+---------------------+
1 row in set (0.00 sec)

Or permanently in your my.cnf:

[mysqld]
**other variables**
default_time_zone='+00:00'

Restart your server, and you will see the change:

mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| +00:00             | +00:00              |
+--------------------+---------------------+
1 row in set (0.00 sec)

mysql> SELECT CURRENT_TIMESTAMP();
+---------------------+
| CURRENT_TIMESTAMP() |
+---------------------+
| 2012-09-25 20:27:50 |
+---------------------+
1 row in set (0.01 sec)


Rolling count of total transactions over time I need to get a set of total transactions over time on a weekly interval. Essentially I need a total-transactions-to-date column. When I group by WEEK(Date) I get the amount of transactions for that week, but need to also get the transactions from any time before that week as well.

So let's say I have a table like this:

TransactionID  | Date
---------------|-----------
             1 | 8-04-2014
             2 | 8-05-2014
             3 | 8-18-2014
             4 | 8-18-2014
             5 | 8-20-2014

I want to have a select statement that would provide me with something like

TotalTransactionsToDate | Week | Year
------------------------|------|------
                      2 |    1 | 2014
                      5 |    3 | 2014

I am using MySql 5.5.38

What you want is called the cumulative sum, you can do something like:

create table transactions (transactionid int, d date);
insert into transactions (transactionid, d)
    values (1, '2014-08-04'),(2,'2014-08-05'), (3, '2014-08-18')
         , (4, '2014-08-18'), (5,'2014-08-20');

select x.y, x.w,  count(1)
from (
   select distinct year(d) as y, week(d) as w
   from transactions
) as x
join transactions y
    on year(y.d) < x.y
    or ( year(y.d) = x.y
     and week(y.d) <= x.w )
group by x.y, x.w;

+------+------+----------+
| y    | w    | count(1) |
+------+------+----------+
| 2014 |   31 |        2 |
| 2014 |   33 |        5 |
+------+------+----------+

I did not see your additional request for 2 2 for 2014. You can do that by replacing:

select distinct year(d) as y, week(d) as w
from transactions

...with an expression that creates the whole domain for weeks. It is often a good idea to create a calendar table that you can use to join against to get reports for missing values etc.


Beginner questions about SQL and relational algebra I'm doing some exercices waiting for the exam. I have some doubt.

RELATIONAL ALGEBRA

  These are the table:

    Newspaper (Cod_n, Name_n, Publisher)

    Article (Cod_a, Title, Topic, Cod_N)

  Request : Find out the name of the newspapers that have published a topic deal with motorcycle at least

  My solution: π_name ( σ_topic = "motorcycle" (newspaper naturaljoin article))

  Book's solution: π_name (newspaper naturaljoin σ_topic = "motorcycle" (article))

Are the solution equivalent? Is the only difference between them the "cost of the operation" ?

  The table is the same of the previous exercice.

  Request: Find out the name of the newspapers that have never published a topic deal with motorcycle

  My solution:   π_name_n (newspaper naturaljoin (σ_topic notegual "motorcycle" (article)

  Book's solution:   π_name_n(newspaper naturaljoin (π_cod_n(newspaper) - π_cod_n (σ_topic = "motorcycle" (Article)

  Are the solution equivalent?

SQL

  These are the table:

    Student(matriculation_number, surname, name)

    Exams(Student, subject, grades, date)

  Request: Find out students (showing matriculation number) that have passed two exams after 1/1/2000 at least

  My Solution:

    select distinct matriculation_number

    from student join exams on matriculation_number = student

    where data > '2000-01-01'

    EXCEPT

    select distinct matriculation_number

    from student join exams on matriculation_number = students

    where data =< '2000-01-01'

  Book's solution:

    select distinct e1.student

    from exams e1 join e2 on e1.student = e2.student

    where e1.subject < > e2.subject

    and e1.date > '2000-01-01'

    and e2.date > '2000-01-01'

Is the solutions equivalent?

Thank you in advantage for your time.
I'm sorry I don't know how to insert relational algebra's symbol.

Your sql query is wrong:

- Assume there is a student s1 who has passed one exam
  after '2000-01-01' and none before.
  Your query results in {s1} - {} = {s1}. This will be a false positive.

- Assume there is a student s1 that passed three exams
  after '2000-01-01' and one exam before.
  Your query results in {s1} - {s1} = {}. This will be a false negative.

I'm having a bit of trouble reading your algebra expressions, but the first one looks ok. It should not matter that you do the selection (topic = "motorcycle") after the join instead of joining on the selection.

The second one can't be right. Assume there's a newspaper that published both an article on motorcycle and an article on something else. Your expression will pick the article on something else and therefore return that newspaper (incorrectly).


Got timeout reading communication packets vs Got error reading communication packets I felt "Got error reading communication packets" is related to max_allowed_packet variable and "Got timeout reading communication packets" is related 'net_read_timout' parameter or network issues.

Let me know whether my understanding is correct or not.

Thanks in advance.

For my situation, the master met with the error timeout, but the slave run the same sql successfully. The sql is about load data, so the master actually finished reading the whole packet?


How a DBA can monitor multiple huge SQL Server databases more than 2 tera bytes I need a some suggestions from you people, I am not a DBA but moving to DBA platform.

I have some questions/clarifications can any body answer? Please.

We have almost 350 servers and more than 2500 database how can we monitor all those in a shot(interms is one DBA can handle or multiple DBA are there in a shift).
How can we monitor all those databases backups and performance.
If per Client individual how many production server will be there.
As an average a DBA how many databases can handle.
How will we provide security for Database?

Can any one answer these questions please? Thanks in advance

Lots of questions so many answers. It depends on so many factors. Budget being one.

First of all I would like to point out that visiting some basic SQL Server courses will vastly increase your understanding of SQL Server and its requirements. IMHO switching from Windows Server Administrator to SQL Server DBA is not just something that can be accomplished by querying Google or asking questions in a forum. You can acquire some knowledge, but without the full picture, you are liable to break something in a productive environment.

Do a search for SQL Server Monitoring Tools in Google. You should get a list of well known products back.
That depends on the monitoring software you purchase. Some can handle these requirements some not. There are also freeware tools available that could help with certain aspects.
Some monitoring products can handle up to 200-250 servers per instance depending on the load and the frequency of polling.
Brent Ozar has an article on this aspect: How many servers can one person manage?, and then again: it depends.
So many aspects here:

Don't hand out the sa password! Never!
Windows Server admins are not DBAs and should not have access to the SQL Server instance. (My opinion)
People outside of the Server or DBA team should not have access to backup drives or to SQL Server database file locations.
Only a qualified DBA should be allowed to be have sysadmin server role
Limit each SQL Login/NT Login to the least possible number of databases.
Limit the SQL Login to different roles depending on usage

DB_NAME_User : db_datareader, db_datawriter
DB_NAME_Dbo : db_owner

Try Transparent Database Encryption
Try Database Backup Encryption

Security depends on the requirements of your business. Ask the head of IT for SLAs or OLAs and any company-wide security policies, etc.

One question you haven't yet asked is: Availability. Will you be required to have Always on Availability Groups?

You asked a lot of questions, I hope you have a general starting point.