| SQL 2012 SQL Log "the log file is not using Unicode format": Posted: 05 Sep 2013 07:56 PM PDT On only one server of many, the SQL log is blank and shows a message "the log file is not using Unicode Format" when viewed via SSMS. The log is in the right location as specified in the service properties and is readable with notepad. Log looks normal, just won't show up in Log File Viewer in SSMS. Any ideas why? SQL Server 2012 in active passive Windows failover cluster on Windows 2012. SQL ver 11.0.3368 This instance is the SQL Server for MS SCCM. It has trace flags -T8295 and -T4199 as service startup parms. These were added by SCCM when its DB's were installed. Thanks |
| SSIS Package produces SQLDUMPER_ERRORLOG.log Posted: 05 Sep 2013 07:20 PM PDT Failing intermittently but more frequently. Package just calls a couple stored procs and exports the results to two Excel spreadsheets. Where to go from here: (D08:FC8) 09/05/13 14:05:57, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Input parameters: 4 supplied (D08:FC8) 09/05/13 14:05:57, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Parameter 1: 1500 (D08:FC8) 09/05/13 14:05:57, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Parameter 2: 0 (D08:FC8) 09/05/13 14:05:57, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Parameter 3: 0:0 (D08:FC8) 09/05/13 14:05:57, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Parameter 4: 0011B568 (D08:FC8) 09/05/13 14:05:57, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Parsed parameters: (D08:FC8) 09/05/13 14:05:57, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ProcessID = 1500 (D08:FC8) 09/05/13 14:05:57, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ThreadId = 0 (D08:FC8) 09/05/13 14:05:57, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Flags = 0x0 (D08:FC8) 09/05/13 14:05:57, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, MiniDumpFlags = 0x0 (D08:FC8) 09/05/13 14:05:57, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, SqlInfoPtr = 0x0011B568 (D08:FC8) 09/05/13 14:05:57, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, DumpDir = <NULL> (D08:FC8) 09/05/13 14:05:57, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ExceptionRecordPtr = 0x00000000 (D08:FC8) 09/05/13 14:05:57, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ContextPtr = 0x00000000 (D08:FC8) 09/05/13 14:05:57, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ExtraFile = <NULL> (D08:FC8) 09/05/13 14:05:57, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, InstanceName = <NULL> (D08:FC8) 09/05/13 14:05:57, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ServiceName = <NULL> (D08:FC8) 09/05/13 14:05:57, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 11 not used (D08:FC8) 09/05/13 14:05:57, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 15 not used (D08:FC8) 09/05/13 14:06:00, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 7 not used (D08:FC8) 09/05/13 14:06:00, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, MiniDump completed: c:\Program Files (x86)\Microsoft SQL Server\100\Shared\ErrorDumps\SQLDmpr0047.mdmp (D08:FC8) 09/05/13 14:06:00, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Location of module 'dbghelp.dll' : 'c:\Program Files (x86)\Microsoft SQL Server\100\Shared\dbghelp.dll' (D08:FC8) 09/05/13 14:06:00, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, File version of module 'c:\Program Files (x86)\Microsoft SQL Server\100\Shared\dbghelp.dll' : '6.8:4.0' (D08:FC8) 09/05/13 14:06:00, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Product version of module 'c:\Program Files (x86)\Microsoft SQL Server\100\Shared\dbghelp.dll' : '6.8:4.0' (D08:FC8) 09/05/13 14:06:00, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Location of module 'sqldumper.exe' : 'c:\Program Files (x86)\Microsoft SQL Server\100\Shared\SQLDUMPER.EXE' (D08:FC8) 09/05/13 14:06:00, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, File version of module 'c:\Program Files (x86)\Microsoft SQL Server\100\Shared\SQLDUMPER.EXE' : '2009.100:1600.1' (D08:FC8) 09/05/13 14:06:00, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Product version of module 'c:\Program Files (x86)\Microsoft SQL Server\100\Shared\SQLDUMPER.EXE' : '10.50:1600.1' (D08:FC8) 09/05/13 14:06:00, ACTION, DTExec.exe, Watson Invoke: No
|
| mySQL for analytics and updating same row Posted: 05 Sep 2013 06:59 PM PDT Looking for the best way to handle using mySQL/PHP for analytics and thus having multiple connections trying to update the same counters in the same row at the same time. The problem being if I have a row like 'year-month-day-hour','hits' and I want to increase hits for every pageview, that's going to be a lot of connections trying to update that same field in the same row. I'm worried about contention and/or writing the wrong values. Also this will be packaged for many clients on different kinds of hosting. Thanks for any suggestions. |
| Why are these two INSERTs deadlocking? Is it the trigger? What does this deadlock trace 1222 log tell me? Posted: 05 Sep 2013 06:22 PM PDT We are seeing intermittent deadlocks in production when receiving multiple simultaneous API requests. Each request basically culminates in an INSERT statement into the same table, which is where we see the deadlock. I wrote a double-threaded console application that can reliably reproduce the issue by simply executing two API requests simultaneously, but only in production, not in staging. (This leads me to believe that there is something about our staging database -- possibly the volume of data, SQL Server 2012 vs 2005, or index tuning -- that differs from production in such a way that the deadlock is avoided. The code is identical, as I believe is the schema.) Since I can now reproduce the deadlock, I was able to convince my boss to enable trace flag 1222 temporarily, and captured the log below: Date,Source,Severity,Message 09/05/2013 16:32:19,spid71,Unknown,DBCC TRACEOFF 1222<c/> server process ID (SPID) 71. This is an informational message only; no user action is required. 09/05/2013 16:30:55,spid17s,Unknown,waiter id=processf34868 mode=X requestType=wait 09/05/2013 16:30:55,spid17s,Unknown,waiter-list 09/05/2013 16:30:55,spid17s,Unknown,owner id=processf35c18 mode=X 09/05/2013 16:30:55,spid17s,Unknown,owner-list 09/05/2013 16:30:55,spid17s,Unknown,objectlock lockPartition=0 objid=428945000 subresource=FULL dbid=8 objectname=MyDB.DomainTransferRAR id=lock120a72c80 mode=X associatedObjectId=428945000 09/05/2013 16:30:55,spid17s,Unknown,waiter id=processf35c18 mode=X requestType=convert 09/05/2013 16:30:55,spid17s,Unknown,waiter-list 09/05/2013 16:30:55,spid17s,Unknown,owner id=processf34868 mode=IX 09/05/2013 16:30:55,spid17s,Unknown,owner-list 09/05/2013 16:30:55,spid17s,Unknown,objectlock lockPartition=0 objid=2096426938 subresource=FULL dbid=8 objectname=MyDB.DomainTransferRANT id=lock11de95480 mode=IX associatedObjectId=2096426938 09/05/2013 16:30:55,spid17s,Unknown,resource-list 09/05/2013 16:30:55,spid17s,Unknown,VALUES (@p0<c/> 09/05/2013 16:30:55,spid17s,Unknown,(@p0 uniqueidentifier<c/>@p1 int<c/>@p2 int<c/>@p3 varchar(8000)<c/>@p4 char(5)<c/>@p5 int<c/>@p6 datetime<c/>@p7 datetime<c/>@p8 varchar(8000)<c/>@p9 char(5)<c/>@p10 int<c/>@p11 datetime<c/>@p12 datetime<c/>@p13 varchar(8000)<c/>@p14 int<c/>@p15 xml<c/>@p16 datetime<c/>@p17 datetime<c/>@p18 varchar(8000)<c/>@p19 datetime<c/>@p20 datetime<c/>@p21 varchar(8000)<c/>@p22 bit<c/>@p23 varchar(8000)<c/>@p24 varchar(8000)<c/>@p25 uniqueidentifier)INSERT INTO [dbo].[DomainTransferRANT]([DomainTransferRANTGUID]<c/> [PrebookedBillPackageId]<c/> [domainID]<c/> [DomainName]<c/> [GainingWNAccountID]<c/> [GainingRegistrantID]<c/> [dtGainingRequestSent]<c/> [dtGainingResponseReceived]<c/> [DomainTransferGainingPartyRespCode]<c/> [LosingWNAccountID]<c/> [LosingRegistrantID]<c/> [dtLosingRequestSent]<c/> [dtLosingResponseReceived]<c/> [DomainTransferLosingPartyRespCode]<c/> [SubmittedBillPackageId]<c/> [ExtraInfo]<c/> [dtRequestSentToRegistry]<c/> [dtResponseFromRegistry]<c/> [DomainTransferRegistryRespCode]<c/> [dtDeleted]<c/> [dtLastChecked]<c/> [DomainTransferStatusCode]<c/> [SendConfirmationForEachDomain]<c/> [SummaryStatus]<c/> [WHOISBeforeTransfer]) 09/05/2013 16:30:55,spid17s,Unknown,inputbuf 09/05/2013 16:30:55,spid17s,Unknown,unknown 09/05/2013 16:30:55,spid17s,Unknown,frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000 09/05/2013 16:30:55,spid17s,Unknown,VALUES (@p0<c/> @p1<c/> @p2<c/> @p3<c/> @p4<c/> @p5<c/> @p6<c/> @p7<c/> @p8<c/> @p9<c/> @p10<c/> @p11<c/> @p12<c/> @p13<c/> @p14<c/> @p15<c/> @p16<c/> @p17<c/> @p18<c/> @p19<c/> @p20<c/> @p21<c/> @p22<c/> @p23<c/> @p24) 09/05/2013 16:30:55,spid17s,Unknown,INSERT INTO [dbo].[DomainTransferRANT]([DomainTransferRANTGUID]<c/> [PrebookedBillPackageId]<c/> [domainID]<c/> [DomainName]<c/> [GainingWNAccountID]<c/> [GainingRegistrantID]<c/> [dtGainingRequestSent]<c/> [dtGainingResponseReceived]<c/> [DomainTransferGainingPartyRespCode]<c/> [LosingWNAccountID]<c/> [LosingRegistrantID]<c/> [dtLosingRequestSent]<c/> [dtLosingResponseReceived]<c/> [DomainTransferLosingPartyRespCode]<c/> [SubmittedBillPackageId]<c/> [ExtraInfo]<c/> [dtRequestSentToRegistry]<c/> [dtResponseFromRegistry]<c/> [DomainTransferRegistryRespCode]<c/> [dtDeleted]<c/> [dtLastChecked]<c/> [DomainTransferStatusCode]<c/> [SendConfirmationForEachDomain]<c/> [SummaryStatus]<c/> [WHOISBeforeTransfer]) 09/05/2013 16:30:55,spid17s,Unknown,frame procname=adhoc line=1 stmtstart=738 stmtend=2322 sqlhandle=0x0200000038d7940c60b76abf51c3cf1bc774fe27ba136260 09/05/2013 16:30:55,spid17s,Unknown,select @iLockDummy = 1 from DomainTransferRANT with (TABLOCKX<c/> HOLDLOCK) OPTION(EXPAND VIEWS) 09/05/2013 16:30:55,spid17s,Unknown,frame procname=MyDB.tri_DomainTransferRANT_InsUpd line=68 stmtstart=10336 stmtend=10544 sqlhandle=0x03000800c8c31e75b259fa002fa200000000000000000000 09/05/2013 16:30:55,spid17s,Unknown,executionStack 09/05/2013 16:30:55,spid17s,Unknown,process id=processf35c18 taskpriority=0 logused=2992 waitresource=OBJECT: 8:2096426938:0 waittime=171 ownerId=26880196297 transactionname=user_transaction lasttranstarted=2013-09-05T16:30:55.710 XDES=0x1c7b18b60 lockMode=X schedulerid=2 kpid=34404 status=suspended spid=69 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2013-09-05T16:30:55.763 lastbatchcompleted=2013-09-05T16:30:55.710 clientapp=.Net SqlClient Data Provider hostname=WEB1 hostpid=13216 loginname=client isolationlevel=serializable (4) xactid=26880196297 currentdb=8 lockTimeout=4294967295 clientoption1=673316896 clientoption2=128056 09/05/2013 16:30:55,spid17s,Unknown,VALUES (@p0<c/> 09/05/2013 16:30:55,spid17s,Unknown,(@p0 uniqueidentifier<c/>@p1 int<c/>@p2 int<c/>@p3 varchar(8000)<c/>@p4 char(5)<c/>@p5 int<c/>@p6 datetime<c/>@p7 datetime<c/>@p8 varchar(8000)<c/>@p9 char(5)<c/>@p10 int<c/>@p11 datetime<c/>@p12 datetime<c/>@p13 varchar(8000)<c/>@p14 int<c/>@p15 xml<c/>@p16 datetime<c/>@p17 datetime<c/>@p18 varchar(8000)<c/>@p19 datetime<c/>@p20 datetime<c/>@p21 varchar(8000)<c/>@p22 bit<c/>@p23 varchar(8000)<c/>@p24 varchar(8000)<c/>@p25 uniqueidentifier)INSERT INTO [dbo].[DomainTransferRANT]([DomainTransferRANTGUID]<c/> [PrebookedBillPackageId]<c/> [domainID]<c/> [DomainName]<c/> [GainingWNAccountID]<c/> [GainingRegistrantID]<c/> [dtGainingRequestSent]<c/> [dtGainingResponseReceived]<c/> [DomainTransferGainingPartyRespCode]<c/> [LosingWNAccountID]<c/> [LosingRegistrantID]<c/> [dtLosingRequestSent]<c/> [dtLosingResponseReceived]<c/> [DomainTransferLosingPartyRespCode]<c/> [SubmittedBillPackageId]<c/> [ExtraInfo]<c/> [dtRequestSentToRegistry]<c/> [dtResponseFromRegistry]<c/> [DomainTransferRegistryRespCode]<c/> [dtDeleted]<c/> [dtLastChecked]<c/> [DomainTransferStatusCode]<c/> [SendConfirmationForEachDomain]<c/> [SummaryStatus]<c/> [WHOISBeforeTransfer]) 09/05/2013 16:30:55,spid17s,Unknown,inputbuf 09/05/2013 16:30:55,spid17s,Unknown,unknown 09/05/2013 16:30:55,spid17s,Unknown,frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000 09/05/2013 16:30:55,spid17s,Unknown,VALUES (@p0<c/> @p1<c/> @p2<c/> @p3<c/> @p4<c/> @p5<c/> @p6<c/> @p7<c/> @p8<c/> @p9<c/> @p10<c/> @p11<c/> @p12<c/> @p13<c/> @p14<c/> @p15<c/> @p16<c/> @p17<c/> @p18<c/> @p19<c/> @p20<c/> @p21<c/> @p22<c/> @p23<c/> @p24) 09/05/2013 16:30:55,spid17s,Unknown,INSERT INTO [dbo].[DomainTransferRANT]([DomainTransferRANTGUID]<c/> [PrebookedBillPackageId]<c/> [domainID]<c/> [DomainName]<c/> [GainingWNAccountID]<c/> [GainingRegistrantID]<c/> [dtGainingRequestSent]<c/> [dtGainingResponseReceived]<c/> [DomainTransferGainingPartyRespCode]<c/> [LosingWNAccountID]<c/> [LosingRegistrantID]<c/> [dtLosingRequestSent]<c/> [dtLosingResponseReceived]<c/> [DomainTransferLosingPartyRespCode]<c/> [SubmittedBillPackageId]<c/> [ExtraInfo]<c/> [dtRequestSentToRegistry]<c/> [dtResponseFromRegistry]<c/> [DomainTransferRegistryRespCode]<c/> [dtDeleted]<c/> [dtLastChecked]<c/> [DomainTransferStatusCode]<c/> [SendConfirmationForEachDomain]<c/> [SummaryStatus]<c/> [WHOISBeforeTransfer]) 09/05/2013 16:30:55,spid17s,Unknown,frame procname=adhoc line=1 stmtstart=738 stmtend=2322 sqlhandle=0x0200000038d7940c60b76abf51c3cf1bc774fe27ba136260 09/05/2013 16:30:55,spid17s,Unknown,select @iLockDummy = 1 from DomainTransferRAR with (TABLOCKX<c/> HOLDLOCK) OPTION(EXPAND VIEWS) 09/05/2013 16:30:55,spid17s,Unknown,frame procname=MyDB.tri_DomainTransferRANT_InsUpd line=67 stmtstart=10140 stmtend=10334 sqlhandle=0x03000800c8c31e75b259fa002fa200000000000000000000 09/05/2013 16:30:55,spid17s,Unknown,executionStack 09/05/2013 16:30:55,spid17s,Unknown,process id=processf34868 taskpriority=0 logused=3000 waitresource=OBJECT: 8:428945000:0 waittime=171 ownerId=26880196295 transactionname=user_transaction lasttranstarted=2013-09-05T16:30:55.710 XDES=0x1c7b18370 lockMode=X schedulerid=2 kpid=13932 status=suspended spid=93 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2013-09-05T16:30:55.770 lastbatchcompleted=2013-09-05T16:30:55.710 clientapp=.Net SqlClient Data Provider hostname=MYDB hostpid=13216 loginname=client isolationlevel=serializable (4) xactid=26880196295 currentdb=8 lockTimeout=4294967295 clientoption1=673316896 clientoption2=128056 09/05/2013 16:30:55,spid17s,Unknown,process-list 09/05/2013 16:30:55,spid17s,Unknown,deadlock victim=processf35c18 09/05/2013 16:30:55,spid17s,Unknown,deadlock-list 09/05/2013 16:30:08,spid71,Unknown,DBCC TRACEON 1222<c/> server process ID (SPID) 71. This is an informational message only; no user action is required.
One thing to note is that there is a trigger on the insert into the relevant table. The trigger is necessary to determine a status code for the overall record, which may depend on sibling records in the same table. For a long time we thought the trigger was the cause of the deadlocks, so we added increasingly aggressive locking hints to the trigger, culminating in the current setup where we do a TABLOCKX, HOLDLOCK on the relevant table(s) before the critical section. We figured this would completely prevent the deadlocks, at the expense of some performance, by effectively serializing all inserts. But it seems that is not the case. As I understand it, something else prior to our exclusive table locks must already be holding a shared or update lock. But what? Other info that might help you help me: The table DomainTransferRANT is heavily indexed. Its primary key is a non-clustered GUID. There is a clustered index on another important INT column. And there are 7 other non-clustered indexes. Finally, there are several foreign key constraints. |
| Left join not working like expected Posted: 05 Sep 2013 03:36 PM PDT I need to do a table join to find missing translations in a translation system. Let's say we have the following simplified table (id is the identifier of the string, the actual translation is not included in the example for simplicity's sake): +------+-------+ | lang | id | +------+-------+ | en | hello | | en | world | | fr | hello | | de | world | +------+-------+
My initial thought was to do a simple left join: select a.*, b.* from trans a left join trans b using (id) where a.lang = 'en';
I thought I would get this (so I could add where b.id is null to find the missing translation): +------+-------+------+-------+ | lang | id | lang | id | +------+-------+------+-------+ | en | hello | en | hello | | en | world | en | world | | en | hello | fr | hello | | en | world | NULL | NULL | | en | hello | NULL | NULL | | en | world | de | NULL | +------+-------+------+-------+
What I actually get is this: +------+-------+------+-------+ | lang | id | lang | id | +------+-------+------+-------+ | en | hello | en | hello | | en | world | en | world | | en | hello | fr | hello | | en | world | de | world | +------+-------+------+-------+
What mistake have I made in my way of thinking, and what is the correct query to get the desired output? |
| Client Encoding Mismatch when creating new database Posted: 05 Sep 2013 05:54 PM PDT I'm upgrading a program for work that uses postgres 9.2.4. When the program is run for the first time, it creates a production database, using the existing template database. The program used to run with postgres 8, which uses sql_ascii for its client encoding. Postgres 9 uses UTF8. I know how to fix this on the command line by changing the template database's encoding to sql_ascii, but ideally I'd like to figure out how to get the program to create the new database using the new UTF8 encoding. I found this line in the program sqlString = "CREATE DATABASE " & m_sProdDBName & " WITH OWNER=" & _ m_s4 & " TEMPLATE=" & m_s5 & " ENCODING='sql_ascii' LOCATION= DEFAULT;"
And I changed sql_ascii to UTF8. However, the 'Client encoding mismatch' error gets thrown before that line ever gets executed. It happens when the connection to the template DB is being created. I got a message box to pop up with the connection string right before the connection opens. For some reason, it is hitting the messagebox code twice, even though it should only hit it once. The first time, no connection attempt is made. The second time the program goes on and attempts to open the connection, which is when the error gets thrown. Here are the two connection strings: DRIVER=postgresql;SERVER=Stephane-PC;PORT=5432;UID=postgres;DATABASE=remindex;PWD= DRIVER=postgresql;SERVER=Stephane-PC;PORT=5432;UID=postgres;DATABASE=template1;PWD=
(I know that no password is needed so that isn't the problem). Remindex is the database to be created and template1 is the template DB. In case you need some additional information, the connection object is an ADODB.Connection object and the Provider is MSDASQL. I'm not all that good with database stuff, so I apologize for my lack of knowledge. Any suggestions would be greatly appreciated! |
| Access table converted to system table... Help changing it back? Posted: 05 Sep 2013 01:45 PM PDT Somehow the business unit managed to change a regular table into a system table. Now nobody can read it or edit it. Engine is the owner and nobody has administer permissions. I noticed in MSysObjects that the flag for the table is -2147483648. The only other tables that have that flag are system tables. I feel like I need to change that to 0 and that might solve the problem too. I tried doing like a GRANT SELECT in VBA but it didn't allow me to do that on the table. Not really sure where to go with this. Any suggestions? Let me know if you need more details. |
| Syncing database definitions Posted: 05 Sep 2013 01:40 PM PDT I have a development machine and a production machine both running MySQL. I do changes on the tables from time to time, and usually just do them from the command line. So I have to copy/paste from one shell to the other. Some times I forget to update one of them, this is not a huge deal, but I thought it would be nice to be able to find all definition differences and be able to repair them (always from one to the other, not the other way around). Is there a small and simple tool for this available? A command line tool or something. Not a huge software solution that does a million things. |
| What will be the initial size of my tempdb data file(s) after restart? Posted: 05 Sep 2013 12:45 PM PDT sys.database_files and sys.sysfiles report on size, but that's just the current size of the file. Is there a way to find out what the initial size of the tempdb datafiles will be after it's recreated during a system restart?
|
| Restoring Oracle XE dtabase Posted: 05 Sep 2013 11:57 AM PDT I had Oracle 10G xe installed on my Win Machine. The machine got corrupted and I will have to install Oracle again. I did not take DB back in form of .sql queries. Now how can I use same old Db with new installation? |
| Join on with modified ON Posted: 05 Sep 2013 03:01 PM PDT I am trying to create table with summary of few other tables. All solutions i came up with are very slow and clearly not optimal. Initially I tried with multitude of subquerries but that was very slow. Now I have JOIN but I am not fully grasping which join I should make. 2 Tables are like following Table 1: recent_items ID, timestamp, Text1...TextN Table 2: insider_trades ID, timestamp, Text1...TextM
Where all columns but ID are not related at all and # of cols is not the same. And no IDs are not foreign keys, long story short DB has different collations and i wasn't able to unite them yet. (I didnt create the DB itself) My best attempt with join so far is: SELECT DATE(recent_items.time), SUM(CASE WHEN crit1 = "twitter" THEN 1 ELSE 0 END) Social, # one that works FROM recent_items JOIN (SELECT crit2, crit3, DATE(filedate) AS fdate FROM insider_trades ) AS InInfo ON DATE(recent_items.time) = DATE(InInfo.fdate) GROUP BY DATE(recent_items .time)
I need to do 2 things Sum from table 1 (done) Sum from table 2, irrelevant of ID but based on date specified by SELECT date(table1.time) . Ex. total value of crit2 on certain date or something like SUM(crit2 where crit3 = "A" and date(recent_items.time) = date(InInfo.fdate)) For 2 I tried passing user defined variable but with not much luck either |
| Can I run concurrent backups of multiple read only filegroups? Posted: 05 Sep 2013 01:38 PM PDT I have 62 readonly filegroups in a SQL server 2008 enterprise database. Can I backup multiple read only filegroups at the same time? I would assume this to be the case but given this database is over 20TB in size I do not want to invalidate any files by giving it the ol' college try. |
| To normalize or not Posted: 05 Sep 2013 02:31 PM PDT I'm scraping data from a website and they have pages for Users and Groups. The pages are pretty similar; id, name, description, created date, number of elements and custom background. They also have a Friendlist for Users and a Memberlist for Groups, which besides names, are pretty much the same. I have already normalized that bit. But the User page also contains other snippets. I started sketching out the tables Users, Groups and Page. But now I'm starting to think if I'm over-normalizing this. Because truly, it's not the same data that is being stored. Should I go for 2 tables, each containing fields like page_id, page_name or should I add pivot table that contains all page data (with a side-effect of having some fields NULLable for the Groups pages) |
| Scaling down DB where filesystem is almost full Posted: 05 Sep 2013 10:38 AM PDT Our DBA is on holiday, and since I know 'a bit' of MySQL, they've asked me to fix this. The data slice is at 99%, and I need to know what to get rid of. [root@servername]# find . -type f -size +1000000k -exec ls -lh {} \; | awk '{ print $9 ": " $5 }' ./mysql/stuffInDatabase.ibd: 6.8G Thing is, I've never done this before, and so don't know where to start. Can someone point me in the right direction? Which database do I need to use, the production one or the information_schema? Which query do I use? Do I need to dump and then reimport? Thanks in advance. |
| Design concerns for using 3x bit or one char(1) or one integer in table for holding status of item Posted: 05 Sep 2013 10:17 AM PDT I need to extend my existing table with status column.
I am in doubt how to do that in most appropriate way regarding of future use of that status and performances of existing table. My table is around 3,7 MIL records, and it is heavy read and write table. Table represent stock (warehouse) of items (goods). It holds floats for many kind of prices and amounts, primary key is composite by item_id and warehouse_id. Here is how I see ways to implement adding status of an item. To add three more columns for statuses, each one column will represent some of statues, Something like 'allow_sell','allow_orders','allow_returns' I like this way because it uses BITs, (is it faster than varchar, or integer ?) and easy to understand what each status represents and which combination of statuses are seted on an item. In this way To add one integer column called status_id and to add one more table statuses_of_item where I can hold description of each status and add statues as many as I wish. To add one varchar(10) column where status will be written as dresption ('denay_all','allow_all', 'allow_orders_wo_sels' etc) Have in mind that status is part of business logic and it uses only internal, Representation of statues are not displayed in SIMPLE WAY, statues not shown to users, it is only way how system should handle some situation in different places. I think that 70% of items inside table will have default (same) status. My database system is MS Sql Server 2008R2. How I should design my table to include stautes ? |
| A network-related or instance-specific error occurred Posted: 05 Sep 2013 08:49 PM PDT I have been using a local database on my laptop for the last view weeks. But today I cannot access it. I get the error: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) The system cannot find the file specified" The database connection is: my-pc.C:\DB\DBV8.1.1.MDF.dbo |
| xbase sql query for limiting the output Posted: 05 Sep 2013 01:17 PM PDT I want to query in my xbase database an limit the output like: SELECT * FROM TB_TEST LIMIT 5;
But it does not work on xbase |
| Can I disable journalling to improve performance Posted: 05 Sep 2013 09:42 AM PDT We have a 16 shard MongoDB 2.4 installation running in AWS that is eating money. - The data is volatile, it only gets used within 15 minutes of creation
- If the cluster goes down we wipe it out and restart ~ 30 seconds downtime
I note that the majority of disk stress appears to be journalling Is there any reason I cannot disable journalling? |
| how to Update Value in local tabel when job Finished Posted: 05 Sep 2013 02:53 PM PDT I need to update some values into a database as soon as all the tasks of Executing Service have finished executing there jobs. In other words, I want do something like status when the all jobs finished directly change this status . I think that doing stored procedure like: Update (Table Name ) Set ( Field ) = 1 )
But i don't know, how to execute the stored procedure, when the job finished. |
| Create a secondary read-only copy of DB in a different server Posted: 05 Sep 2013 12:56 PM PDT We have users that query our prod database (mostly ODBC through MS-Access) to create some customs reports. We want to copy our data to a second server (replicate / log shipping / ???) so the load on the prod DB is less. Many of the tables in the Prod DB do not have primary keys and is a SQL Server 2005. Our target DB is SQL Server 2012 Standard (though we can down-grade the destination server if we have to). How could we accomplish that? We've tried replication and failed because of the lack of primary keys. We also tried log-shipping but the second DB cannot be brought online because of the difference in SQL versions. Thanks, Jim |
| How to determine when to reduce physical memory for SQL Server? Posted: 05 Sep 2013 01:04 PM PDT If I repeatedly notice SQL Server reporting a lot of free memory, can I likely remove about that amount of memory from the server without affecting performance? Or, can/should it use more for caching? For this particular example, the server is running SQL Server 2012 Standard Edition on Windows Server 2012. It has 20 GB of physical memory and hosts over 100 GB of data. SQL Server is the only application running on the server. SQLServer;Buffer Manager Buffer cache hit ratio 99.737 % Page life expectancy 874 s SQLServer:Memory Manager Database Cache Memory 6,744 MB Free Memory 5,937 MB Optimizer Memory 5 MB SQL Cache Memory 29 MB Target Server Memory 19,015 MB Total Server Memory 18,407 MB |
| Get missed transactions from slave Posted: 05 Sep 2013 05:55 PM PDT I'm trying to write a disaster recovery plan, I have one way replication with 1 master(A) and 3 slaves(B,C,D) A /|\ / | \ B C D
Let's say my master crash, and I loose all data on that server, I still have 3 slaves, and I'll promote the most up-to-date to be my new master, let say slave B is up to date and C and D are not, at the point I promote B to master my slaves have the follow result for SHOW SLAVE STATUS\G: slaveB> SHOW SLAVE STATUS\G *************************** 1. row *************************** Relay_Master_Log_File: master-bin.000002 Exec_Master_Log_Pos: 1476884 slaveC> SHOW SLAVE STATUS\G *************************** 1. row *************************** Relay_Master_Log_File: master-bin.000002 Exec_Master_Log_Pos: 78684 slaveD> SHOW SLAVE STATUS\G *************************** 1. row *************************** Relay_Master_Log_File: master-bin.000001 Exec_Master_Log_Pos: 746848
In order to promote B to master, I want to apply all missing transactions on C and D, so before B become the new master and start to receive queries from applications, I've all slaves on a consistent state, how can I find the missed transactions from C and D on binary logs from B (I've the --log-slave-updates option enabled on all server). For legacy issues, the envoirement use MySQL 5.0 |
| SQL Server 2012 Express fails at repair install, produces error 5178 Posted: 05 Sep 2013 08:50 AM PDT My SQL Server Express Service will not start up. To produce this problem, I basically cloned my old hard drive (Which had SQL Server Express 2012 installed) to a new hard drive (Seagate Momentus XT 750). EDIT: I am adding info on how I cloned my hard drive as per request of SQLRockStar. I used Seagate DiscWizard. The program was producing errors when trying to clone the HD when using the simple "clone HD" command. So I "cloned" it the following way with the help of SeaGate Tech support: - Place new Hard drive(Momentus XT) in slave slot, Old HD in Master)
- Create backup image of old HD and save on Old HD.
- Create Seagate DiscWizard Bootable CD
- Physically Swap both hard drive positions, (old now is in slave, new is in Master)
- Boot with Seagate DiscWizard Bootable CD and restore backup image of old HD onto new HD.
Afterwards, I tried starting SQL Server Express 2012 on my Momentus XT and it would not start. So, I tried performing a repair installation of SQL Server, and it failed: see summary below: Overall summary: Final result: Failed: see details below Exit code (Decimal): -2061893608 Start time: 2013-08-12 15:53:13 End time: 2013-08-12 16:13:13 Requested action: Repair Setup completed with required actions for features. Troubleshooting information for those features: Next step for SQLEngine: Use the following information to resolve the error, and then try the setup process again. Next step for Replication: Use the following information to resolve the error, and then try the setup process again. Machine Properties: Machine name: MATT-LAPTOP Machine processor count: 8 OS version: Windows 7 OS service pack: Service Pack 1 OS region: United States OS language: English (United States) OS architecture: x64 Process architecture: 64 Bit OS clustered: No Product features discovered: Product Instance Instance ID Feature Language Edition Version Clustered SQL Server 2008 Management Tools - Basic 1033 Express Edition 10.0.1600.22 No SQL Server 2012 SQLEXPRESS MSSQL11.SQLEXPRESS Database Engine Services 1033 Express Edition 11.0.2316.0 No SQL Server 2012 SQLEXPRESS MSSQL11.SQLEXPRESS SQL Server Replication 1033 Express Edition 11.0.2316.0 No SQL Server 2012 Management Tools - Basic 1033 Express Edition 11.0.2316.0 No SQL Server 2012 LocalDB 1033 Express Edition 11.0.2318.0 No Package properties: Description: Microsoft SQL Server 2012 ProductName: SQL Server 2012 Type: RTM Version: 11 SPLevel: 0 Installation location: c:\215ca8b216eb992f2f4a\x64\setup\ Installation edition: Express User Input Settings: ACTION: Repair AGTDOMAINGROUP: <empty> AGTSVCACCOUNT: NT AUTHORITY\NETWORK SERVICE AGTSVCPASSWORD: <empty> AGTSVCSTARTUPTYPE: Disabled ASCONFIGDIR: Config ASSVCACCOUNT: <empty> ASSVCPASSWORD: <empty> CLTSTARTUPTYPE: 0 CLTSVCACCOUNT: <empty> CLTSVCPASSWORD: <empty> CONFIGURATIONFILE: CTLRSTARTUPTYPE: 0 CTLRSVCACCOUNT: <empty> CTLRSVCPASSWORD: <empty> ENU: true FAILOVERCLUSTERGROUP: <empty> FAILOVERCLUSTERNETWORKNAME: <empty> FTSVCACCOUNT: <empty> FTSVCPASSWORD: <empty> HELP: false INDICATEPROGRESS: false INSTANCENAME: SQLEXPRESS ISSVCACCOUNT: NT AUTHORITY\Network Service ISSVCPASSWORD: <empty> ISSVCSTARTUPTYPE: Automatic QUIET: false QUIETSIMPLE: false SQLSVCACCOUNT: NT Service\MSSQL$SQLEXPRESS SQLSVCPASSWORD: <empty> UIMODE: AutoAdvance X86: false Configuration file: C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log\20130812_155231\ConfigurationFile.ini Detailed results: Feature: Management Tools - Basic Status: Passed Feature: Database Engine Services Status: Failed: see logs for details Reason for failure: An error occurred during the setup process of the feature. Next Step: Use the following information to resolve the error, and then try the setup process again. Component name: SQL Server Database Engine Services Instance Features Component error code: 0x851A0018 Error description: Could not find the Database Engine startup handle. Error help link: http://go.microsoft.com/fwlink?LinkId=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=11.0.2316.0&EvtType=0xD15B4EB2%400x4BDAF9BA%401306%4024&EvtType=0xD15B4EB2%400x4BDAF9BA%401306%4024 Feature: SQL Server Replication Status: Failed: see logs for details Reason for failure: An error occurred for a dependency of the feature causing the setup process for the feature to fail. Next Step: Use the following information to resolve the error, and then try the setup process again. Component name: SQL Server Database Engine Services Instance Features Component error code: 0x851A0018 Error description: Could not find the Database Engine startup handle. Error help link: http://go.microsoft.com/fwlink?LinkId=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=11.0.2316.0&EvtType=0xD15B4EB2%400x4BDAF9BA%401306%4024&EvtType=0xD15B4EB2%400x4BDAF9BA%401306%4024 Feature: SQL Browser Status: Passed Feature: SQL Writer Status: Passed Feature: LocalDB Status: Passed Feature: SQL Client Connectivity Status: Passed Feature: SQL Client Connectivity SDK Status: Passed Feature: Setup Support Files Status: Passed Rules with failures: Global rules: There are no scenario-specific rules. Rules report file: C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log\20130812_155231\SystemConfigurationCheck_Report.htm The following warnings were encountered while configuring settings on your SQL Server. These resources / settings were missing or invalid so default values were used in recreating the missing resources. Please review to make sure they don't require further customization for your applications: Service SID support has been enabled on the service. Service SID support has been enabled on the service. The following resources could not be configured during repair without additional user input. Review the warnings to understand your next steps: The service failed to start for an unknown reason. For more information, see the event logs and the SQL Server error logs.
I looked at the error log and it said Error: 5178, Severity: 16, State: 1 Cannot use file 'c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\master.mdf' because it was originally formatted with sector size 4096 and is now on a volume with sector size 3072. Move the file to a volume with a sector size that is the same as or smaller than the original sector size. I read that last error message and am really confused. I'm led to believe that this is a problem with SQL Server, My HD has 4096 sector size. UPDATE: More information: I have discovered that My old hard Drive was 512 physical sector size and my new HD is 4096 sector size. I hear that there are conversion issues between the two sector sizes, but SQL Server is the only program that is producing errors on my system, I don't understand it. UPDATE: I have attempted to uninstall SQL Server and reinstall it. It did not succeed in installing the instance. |
| Ignore login mappings during Dacpac compare Posted: 05 Sep 2013 10:07 AM PDT We're trying to automate the deployment of databases through development, testing and production. Our approach, using SSDT, is to compare DACPACs from the different environment to generate update scripts, and as a final step, map the correct windows logins to the correct users. /p:IgnoreUserLoginMappings Looking at the documentation for SqlPackage.exe there is a property IgnoreUserLoginMappings that seems to imply that it would ignore mappings, so that they are not included in the dacpac and hence not updated during the comparison and script generation. But, when we use this property during our extract the users generated are WITHOUT LOGIN, like this in the dacpac: <Element Type="SqlUser" Name="[Domain\Account]"> <Property Name="IsWithoutLogin" Value="True" /> <Relationship Name="DefaultSchema"> <Entry> <Annotation Type="PersistedResolvableAnnotation" Name="[Domain\Account]"> <Property Name="TargetTypeStorage" Value="SqlSchema" /> </Annotation> </Entry> </Relationship> </Element>
which is NOT what we want! The subsequent generated update looks something like this: CREATE USER [Domain\Account] WITHOUT LOGIN WITH DEFAULT_SCHEMA = [Domain\Account];
Are we doing something wrong or is this a bug, I've searched and can't see much info on this property, is anyone using it that can help, or is there another approach? Related question: here |
| creating a report on the mysql jasperReport server Posted: 05 Sep 2013 10:18 AM PDT I have mysql local database, in it I have sample data mart. I have installed jasperReport server to my computer. Just using this tool, I want connect to the local mysql database and just create a pdf report or any report without using any tool except jasperReport server. Can I do ? HOw? Note; report will be directly constructed on the whole data reside in the mysql database. (No filtering) I want to see the report on the jasperReport report page. If any change occur on the mysql database, it should be carried out on the report page of the JAsperReport server |
| Why does this procedure raise a privilege error? Posted: 05 Sep 2013 09:18 AM PDT I am having trouble getting the following procedure to run on a remote MySQL database - the given error is privilege based (#1227). Locally, the procedure runs fine. QUESTIONS - Could somebody help me understand which specific part of this code raises this error, i.e. requires the said privilege?
- Is there any way I could have corresponding functionality without a privileged call?
|
| MySQL5.6 on Mac OS X 10.6.8 problems when granting permissions to root Posted: 05 Sep 2013 11:18 AM PDT I'm having serious problems with a MySQL 5.6 instance on a Mac Server. We had to upgrade MySQL to a newer version, but it turned to be a bad idea, as we lost control to it. We had a backup of the /data directory and the my.cnf file as well. However, when setting an init_file to restore the previous password and its permissions. So we created a text file with this content: UPDATE mysql.user SET Password=PASSWORD('myOriginalPassword') WHERE User='root'; FLUSH PRIVILEGES;
We have double checked the init_file permissions (we even gave it a chmod 777, to make sure it worked), but something is not working. When we run mysqld_safe --init_file=/usr/local/mysql/bin/init_file it tries to start the server, but then it shuts itself down. The log is as follows: 130308 13:23:52 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data 2013-03-08 13:23:53 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2013-03-08 13:23:53 18979 [Warning] Setting lower_case_table_names=2 because file system for /usr/local/mysql/data/ is case insensitive 2013-03-08 13:23:53 18979 [Note] Plugin 'FEDERATED' is disabled. 2013-03-08 13:23:53 18979 [Note] InnoDB: The InnoDB memory heap is disabled 2013-03-08 13:23:53 18979 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2013-03-08 13:23:53 18979 [Note] InnoDB: Compressed tables use zlib 1.2.3 2013-03-08 13:23:53 18979 [Note] InnoDB: CPU does not support crc32 instructions 2013-03-08 13:23:53 18979 [Note] InnoDB: Initializing buffer pool, size = 128.0M 2013-03-08 13:23:53 18979 [Note] InnoDB: Completed initialization of buffer pool 2013-03-08 13:23:53 18979 [Note] InnoDB: Highest supported file format is Barracuda. 2013-03-08 13:23:53 18979 [Note] InnoDB: 128 rollback segment(s) are active. 2013-03-08 13:23:53 18979 [Note] InnoDB: Waiting for purge to start 2013-03-08 13:23:53 18979 [Note] InnoDB: 1.2.10 started; log sequence number 19440939 2013-03-08 13:23:53 18979 [ERROR] /usr/local/mysql/bin/mysqld: unknown option '--skip-locking' 2013-03-08 13:23:53 18979 [ERROR] Aborting 2013-03-08 13:23:53 18979 [Note] Binlog end 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'partition' 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA' 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES' 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES' 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS' 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN' 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_SYS_FIELDS' 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS' 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_SYS_INDEXES' 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS' 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_SYS_TABLES' 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE' 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE' 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_FT_CONFIG' 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED' 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_FT_DELETED' 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_FT_INSERTED' 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD' 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_METRICS' 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS' 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU' 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE' 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET' 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX' 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET' 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_CMPMEM' 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_CMP_RESET' 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_CMP' 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_LOCK_WAITS' 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_LOCKS' 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'INNODB_TRX' 2013-03-08 13:23:53 18979 [Note] Shutting down plugin 'InnoDB' 2013-03-08 13:23:53 18979 [Note] InnoDB: FTS optimize thread exiting. 2013-03-08 13:23:53 18979 [Note] InnoDB: Starting shutdown... 2013-03-08 13:23:54 18979 [Note] InnoDB: Shutdown completed; log sequence number 19440949 2013-03-08 13:23:54 18979 [Note] Shutting down plugin 'BLACKHOLE' 2013-03-08 13:23:54 18979 [Note] Shutting down plugin 'ARCHIVE' 2013-03-08 13:23:54 18979 [Note] Shutting down plugin 'MRG_MYISAM' 2013-03-08 13:23:54 18979 [Note] Shutting down plugin 'MyISAM' 2013-03-08 13:23:54 18979 [Note] Shutting down plugin 'MEMORY' 2013-03-08 13:23:54 18979 [Note] Shutting down plugin 'CSV' 2013-03-08 13:23:54 18979 [Note] Shutting down plugin 'sha256_password' 2013-03-08 13:23:54 18979 [Note] Shutting down plugin 'mysql_old_password' 2013-03-08 13:23:54 18979 [Note] Shutting down plugin 'mysql_native_password' 2013-03-08 13:23:54 18979 [Note] Shutting down plugin 'binlog' 2013-03-08 13:23:54 18979 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete 130308 13:23:54 mysqld_safe mysqld from pid file /usr/local/mysql/data/server.myServerName.com.pid ended
The problem may definitely come from the [Error] lines, but as we haven't used the --skip-locking, it's just confusing. If we try to log into mysql using mysql -r -p and set the password we used, it can't connect to the server, as it is not started. Any ideas where this problem may come from? |
| Mysql Can't create test file + 1067 Error Posted: 05 Sep 2013 12:17 PM PDT I have installed mysql-5.5.30-win32. All works fine until i create my own my.ini configuration file. When I am trying to start the service using CMD (I do run the cmd as administrator, and install the service before): Error using mysqld --console [Warning] Can't create test file C:\WebSerer\MySQL\data\x-PC.lower-test [Warning] Can't create test file C:\WebSerer\MySQL\data\x-PC.lower-test mysqld: Can't change dir to 'C:\WebSerer\MySQL\data\' (Errcode: 2) [ERROR] Aborting [Note] mysqld: Shutdown complete
Error using NET START MySQL The MySQL service is starting... The MySQL service could not be started. A system error has occurred. System error 1067 has occurred. The process terminated unexpectedly.
The configuration (my.ini) file: [mysql] # CLIENT # port = 3306 socket = C:\WebSerer\MySQL\data\mysql.sock [mysqld] # GENERAL # user = mysql default_storage_engine = InnoDB socket = C:\WebSerer\MySQL\data\mysql.sock pid_file = C:\WebSerer\MySQL\data\mysql.pid # MyISAM # key_buffer_size = 32M myisam_recover = FORCE,BACKUP # SAFETY # max_allowed_packet = 16M max_connect_errors = 1000000 skip_name_resolve sql_mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY sysdate_is_now = 1 innodb = FORCE innodb_strict_mode = 1 # DATA STORAGE # datadir = C:\WebSerer\MySQL\data\ # BINARY LOGGING # log_bin = C:\WebSerer\MySQL\data\mysql-bin expire_logs_days = 14 sync_binlog = 1 # CACHES AND LIMITS # tmp_table_size = 32M max_heap_table_size = 32M query_cache_type = 0 query_cache_size = 0 max_connections = 500 thread_cache_size = 50 open_files_limit = 65535 table_definition_cache = 1024 table_open_cache = 2048 # INNODB # innodb_log_files_in_group = 2 innodb_log_file_size = 128M innodb_flush_log_at_trx_commit = 1 innodb_file_per_table = 1 innodb_buffer_pool_size = 2G # LOGGING # log_error = C:\WebSerer\MySQL\data\mysql-error.log log_queries_not_using_indexes = 1 slow_query_log = 1 slow_query_log_file = C:\WebSerer\MySQL\data\mysql-slow.log
I was looking the internet for solution for both of this errors. If anyone can help me please I will be very thankful, Thank you all and have a nice day.
Edit This is the new Error using mysqld --console [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --ex plicit_defaults_for_timestamp server option (see documentation for more details). [Warning] Can't create test file C:\WebSerer\MySQL\data\iSession-PC.lower-t est [Warning] Can't create test file C:\WebSerer\MySQL\data\iSession-PC.lower-t est mysqld: Can't change dir to 'C:\WebSerer\MySQL\data\' (Errcode: 2 - No such file or directory) [ERROR] Aborting [Note] Binlog end [Note] mysqld: Shutdown complete
Also i tryed to change the location of the data dir at the my.ini file, The new path for the datadir is located at my desktop, Now i have another different error i have seen previously when i remove the datadir line, This is the error: **[Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --ex plicit_defaults_for_timestamp server option (see documentation for more details).** **mysqld: File 'C:\WebSerer\MySQL\data\mysql-bin.index' not found (Errcode: 2 - No such file or directory)** **[ERROR] Aborting** **[Note] Binlog end** **[Note] mysqld: Shutdown complete**
So as i understand, The first error was becouse of permission problems, There is mysql user inside the my.ini and i should change the mysql user permission so it can write, The second error is after i changed the location of the data folder to my desktop so it can write now, but now there is another error appear, If anyone can help me please with the permission to mysql user and the new error, and have a nice day. |
| Is there a way to create a re-usable base table definition in InfoSphere Data Architect? Posted: 05 Sep 2013 08:45 AM PDT Our company has chose to use IBM's InfoSphere Data Architect (IDA) for data architecting regarding our data warehouse and data marts. The data architects originally designed several "re-usable" tables for our marts. In other words a table like CUSTOMER_DIMENSION may reside structure-wise in more than one mart. Each mart will have their own copy of their data. I believe to make things easy, they defined all the tables (dimensions and facts) within the same "schema" that they called DB2ADMIN within IDA. Since a mart can only have one fact table, I have to look at the diagrams of the table relationships to the fact tables to determine which tables should get generated into a DDL file for a particular mart. As of right now each mart physically resides within its own schema within one database. So not only do I have to manually pick the tables for DDL generation, but I have to manually tweak each DDL file to override the schema. This is not ideal as it is an error-prone process. Is there a way to have a "base" definition of a table and then inherit it or whatever into the real table within IDA? What I'm looking to do is have schema definitions within the tool same as what we have on our server. I would like only the applicable tables to show up within each schema (as a mart should be - one fact and only its dimension tables it cares about). But to save on work, I'd prefer if the table definitions were in one place. So the architects could make one change, but we could more easily generate our tables. Is this possible? Are there any gurus with IDA out there? |
No comments:
Post a Comment